DBMS 1 Lesson 4 part 2

If You haven't studied Lessons posted earlier click below Links
 Lesson 1,Lesson 2,Lesson 3,Lesson 4 part 1

2NF
After completing 1NF,result of it is Normalized into 2NF.
1NF හි ලැබුනු ප්‍රතිපලය 2NF කිරීමට යොදා ගැනේ.

 Functional Dependency

A --------------> B,C
We call B,C are functionally depend on A.Here A is called Determinant & B,C are called Dependent.
B හා C හි අගයන් A මත රදා පවති.මෙහි A,Determinant වන අතර B,C; Dependent වේ.

Cno------------------>Cname,CLocation,Mno,Mname - Partial Dependency
Fno,Cno------------>Takings -Full Functional Dependency

Partial Dependency= Non Key depend on Part of the Composite P/K(Primary Key)
P/K හෝ Composite P/K නොවන අගයක් Composite P/K හි කොටසක් මත රදා පැවතීම
Eg: Non Keys(P/K හෝ Composite P/K නොවන අගයන්) Cname,CLocation,Mno,Mname & Part of the Composite P/K is Cno.

Full Functional Dependency=Non Key depend on Composite P/K
P/K හෝ Composite P/K  නොවන අගයක් සම්පූර්ණ Composite P/K එක මත රදා පැවතීම
Eg: Non Key is Takings & Composite P/K is Fno,Cno.

Answer:
------------------------------------------------------------------------------------------------------------------------

•     Cinema_2(Cno,Cname,CLocation,Mno,Mname) 
•     Showing_2(Fno*,Cno*,Takings)
•     Film_2(Fno,Fname)   

------------------------------------------------------------------------------------------------------------------------------------------------

2NF හීදි Cno මත Cname,CLocation රැදී පවතිනවා. Cname,CLocation මත Mno,Mname රැදී පවතින බැවින් Cno මත Cname,CLocation,Mno,Mname රැදී පවති.නමුත් Takings රැදී පවතින්නැ.

      උදා:   Fno : 15                                     Fno : 45 

                Cno : 1                                       Cno : 1       

               Cname : Odeon                         Cname : Odeon 

               CLocation : NewCastle            CLocation : NewCastle 

             Mno : 1                                      Mno : 1 

             Mname : Green                       Mname : Green

             Takings : 220                           Takings : 600

       එම නිසා ඒ සියල්ල ඇතුලත්ව Cinema_2 නමින් 2NF හීදි ලිවිය යුතුය.මෙහිදි Cno එක Key එකක් වේ.අනෙක් ඒවා NonKey වේ.1NF මගින් ලියූ Showing_1 හී Cname,CLocation,Mno,Mname සියල්ල ඇතුලත්ව ඇත.අප 2NF හීදි Takings ඇර අනෙක් සියල්ල Cinema_2 හීදි ලියූ බැවින් -Showings තුල ඉතිරි වන්නේ Takings පමණයි.එම නිසා එය Showing_2 ලෙස ගෙන ලියා දක්වනවා.Film_1 එක Film_2 ලෙස කිසිදු වෙනසකින් තොරව 2NF හී ලියා දක්වනවා.

In 2NF Cname,CLocation depends on Cno.As well as Mno,Mname depends on Cname & CLocation.Therefore we can say Cname,CLocation,Mno,Mname depends on Cno.But Takings doesn't depend on any.

Eg:Fno : 15                                     Fno : 45 

                Cno : 1                                       Cno : 1       

               Cname : Odeon                         Cname : Odeon 

               CLocation : NewCastle            CLocation : NewCastle 

             Mno : 1                                      Mno : 1 

             Mname : Green                       Mname : Green

             Takings : 220                           Takings : 600

Therefore new Cinema_2 should be written including those in 2NF. In here Cno referred to be a Key and others are NonKeys.But in Showing_1 written in 1NF includes all of these.In 2NF we write Cno,Cname,CLocation,Mno,Mname in Cinema_2 except Takings.Thereby Only Takings remain in Showing_1 came from 1NF.Therefore we give a new name to Showing as Showing_2 in 2NF.As there are no changes in Film_1,we write same thing in 2NF as Film_2.

3NF
After completing 2NF,result of it is Normalized into 3NF.
 Transitive Dependency

Non Key depend on a Non Key
Mno ------------------> Mname 

When Mno changes Mname changes respectively.But both of these are not keys(P/K or Composite P/K).This is called Transitive Dependency.
Mno වෙනස් වෙද්දි Mname ඒ අනුව වෙනස් වෙනවා.නමුත් Mno හා Mname ,NonKeys.ඒ කියන්නේ දෙකම P/K හෝ Composite P/K නෙවෙයි.මේකට තමයි Transitive Dependency කියන්නේ.
Eg : When Mno is 1 Mname is always Green
       When Mno is 2 Mname is always Wood

Answer:
------------------------------------------------------------------------------------------------------------------------

• Manager_3(Mno,Mname)

• Cinema_3(Cno,Cname,CLocation,Mno*)

• Showing_3(Fno*,Cno*,Takings)

• Film_3(Fno,Fname)

-----------------------------------------------------------------------------------------------------------------------------------------------

Here we write a new one in 3NF as Manager_3 including Transitive Dependencies.From 2NF we already have Cinema_2.It Includes both Mno & Mname.Here Mno is a Key.Therefore we write new Cinema_3 excluding Mname but including Mno(Refer a similar case in 2NF Cno is Written in both Cinema_2 & Showing_2).As usual no changes happen to Showing & Film thereby they are written with different names without any change.

මෙහිදි Mno හා Mname ඇතුලත්ව Manager_3 නමින් 3NF හීදි ලියා දක්වයි.2NF මගින් අප ලියූ Showing_2 හී Mno හා Mname ඇතුලත්ව ඇත.Mno එක Key එකක් වන නිසා එය නවතම Cinema_3 හීදි ලියා දක්වන නමුත් Mname ලියනු නොලැබේ(මේ හා සමාන ලිවීමක් Cinema_2 හා Showing_2 ,2NF හීදි දැක ගත හැක.)මෙහිදි Film හා Showing කිසිදු බලපෑමක් නොමැති බැවින් Film_3 හා Showing_3 ලෙස ලියනු ලැබේ.

-------------------------------------------------------------------------------------------------------------------------------------------

Final Complete Answer For Normalizing the table given upto 3NF

1NF

• Film_1(Fno,Fname)
• Showing_1(Fno*,Cno,Cname,CLocation,Mno,Mname,Takings)

2NF

• Cinema_2(Cno,Cname,CLocation,Mno,Mname)
• Showing_2(Fno*,Cno*,Takings)
• Film_2(Fno,Fname)

3NF

• Manager_3(Mno,Mname)
• Cinema_3(Cno,Cname,CLocation,Mno*)
• Showing_3(Fno*,Cno*,Takings)
• Film_3(Fno,Fname)

Click Here To Download This Lesson in PDF Format

DBMS 1 Lesson 4 part 1

මේක හදන්න ගොඩක් වෙලා ගියත්මේ පෝස්ටුව සින්හලෙන් හා ඉන්ග්‍රීසි භාෂා දෙකෙන්ම ඉදිරිපත් කරන්න හිතුවා ගොඩක් අය ඉල්ලපු නිසා.අද කතා කරන්නේ Normalization ගැන.
Today we'll talk about  Normalization.

If You haven't studied Lessons posted earlier click below Links Lesson 1,Lesson 2,Lesson 3

Lesson 4:

මුලින්ම Normalization  කියන්නේ මොකක්ද කියලා හා ඇයි පාවිච්චි කරන්නේ  කියලා බලමු.

First of all what is  Normalization and why do we use it??

Relation එකක,එකට තියෙන උපලක්ශන(attributes) කොහොමද කාණ්ඩ වලට දාන්නේ කියන එක හොයන ක්‍රමයක්.තව විදිහකින් කිව්වොත් relation එකක තියෙන anomalies අඩුකරලා පිලිවෙලක් සහිත relation හදා ගන්න ක්‍රමවේදයක්.
මේ ක්‍රමය භාවිතා කලාම එකම දත්තය නැවත  නැවත ගබඩා වීම වලක්වා relation එකක Insert,Update,Delete වැනි ක්‍රියාවලි කරදරයකින් තොරව කරගැනීමට උදව් වෙනවා.මේ නිසා අනවශය දත්ත ගබඩා කිරීම් වලක්වනවා.

Process for deciding which attributes should be grouped together in a relation.Normalization is the process of decomposing relations with anomalies to produce smaller,well structured relations.By using this we can reduce redundancy and allow users to perform Insert,Update,Delete operations in a relation without any problem.This saves the storage space of DB.

පෙර දැන ගත යුතු දෙවල්:

Relation : තිරස් පෙල (rows) , සිරස් පෙල (columns) ඇති දත්ත සහිත ද්විමාන වගුවක්.
                     Two dimensional table consisting of rows,columns of data.

More details will be discussed in Relational Database Model Lesson.
මේ ගැන වැඩි විස්තර Relational Database Model පාඩමේදි  කතා කරමු.

Eg for Relation:

Firstname	Lastname	Points
Viraj	Wick	10
Tashu	Uss	94
Mitu	Sari	80

Anomaly : කොහේහරි දත්තයක් තැන් දෙකක තියෙනවනම් එම දත්තය වෙනස් කිරීමේදි එම ස්ථාන දෙකම වෙනස් කල යුතුවේ නැතහොත් දෝශයක් හට ගනී.එම දෝශ සහගත තත්වය Anomaly නම් වේ.
Anomaly is an error or inconsistancy that may result when user attempts to update a relation that contains redundant data.

Anomaly වර්ග 3 කි/Anomaly are 3 types.

• Insertion Anomaly - මූල දත්තයක් නොමැති වීම නිසා දත්ත නැවත එකතු කිරීමට ඇති නොහැකියාව.Inability to add data to the DB due to absence of related data.
• Update Anomaly - දත්තයක් වෙනස් කිරීමේදි එය තවත් තැනක පිහිටයි නම් දත්ත නොගැලපීම් ඇතී වීම්.A change in redundant data may cause data inconsistent.
• Deletion Anomaly - දත්ත අතර සබැදියාවක් පවතින විට එම දත්තය මැකීමේදි එහි අනෙකුත් සබැදි දත්තත් මකී යාම.Unintended loss of data due to deletion of related data.

Normalization Process/ක්‍රියාවලිය

Now we are going to learn normalization through a example.උදාහරණයක් මගින් normalization ඉගෙන ගනිමු.

සැ.යු. : මෙම පෝස්ටු මගින් 3NF  දක්වා පමනක් සාකච්චා කෙරේ
Note: By these posts we'll discuss upto 3NF only
Table with repeating groups
එකම දත්ත නැවත නැවත සදහන්ව ඇති වගුවක් :

1NF

Answer:
------------------------------------------------------------------------------------------------------------------

1.Single valued attributes with a key
          •Film_1(Fno, Fname)
2.Multi valued attributes with a Composite key
        •Showing_1(Fno*,Cno,Cname,CLocation,Mno,Mname,Takings)

------------------------------------------------------------------------------------------------------------------


1.මෙහි Film_1,Showing_1  සදහා කැමති නම් 2 ක් භාවිතා කල හැක.වගුවෙහි Fno තීරුව දෙස බැලූ විට එහි දත්ත නැවත නැවත මුද්‍රණය වී නැත.Fname හීදිද එසේමය.එනම් මේවා Single Valued Attributes වේ.එහි Fno එක මත Fname රැදී පවති.

උදා : Fno තීරුවේ 15 නම් Fname එක Arthur විය යුතුමයි.එය Titanic විය නොහැකිය.

එනම් Fno ක Primary Key එකක් වේ.එය යටින් ඉරක් ඇදීමට හේතුව එයයි.

1.You can use any name instead of Film_1,Showing_1.When you go through the Fno column you can see only single data not any duplicates.Same as in Fname.Therefore these are called Single Valued Attributes and also Fname depends on Fno.

Eg : Arthur in Fname given a Fno 15.There is no any other name given to number 15 in Fname.
Thereby we called Fno as a Primary Key.

2.Cno,Cname,CLocation,Mno,Mname,Takings සියලුම තීරු සදහා එක් අගයකට වඩා වැඩි ප්‍රමාණයක් ඇත.එනම් ඒවා Multi Valued Attributes වේ.එසේම Cno එක මත Cname රදා පවති.
    
උදා :Cno 1 වන විට Cname Odeon වේ.2 වන විට ABC වේ.එම සම්බන්ධතාවය තීරු දෙකේම දැකිය හැක.

Fno,Cno Primary Key වන බැවින් දෙකම අලලා යටින් ඉරක් අදි.එය Composite Primary Key නම් වේ.

2.When you go through columns like Cno ,Cname,Clocation,Mno,Mname,Takings, all have more than 1 data.Therefore these are Multi Valued Attributes.Like earlier Cname depends on Cno.

Eg : When Cno is 1 Cname should be Odeon in all the places where Cno is 1.

Therefore when we are writing Fno,Cno together we consider it as a Composite Primary Key and underline both together.



Click Here To Download This Lesson in PDF Format

Table in 2NF & 3NF will be done in next post.Keep in touch..

2NF හා 3NF ඊලග පෝස්ටුවෙන්..

DBMS 1 Lesson 3

Today I’ll talk about Database Architecture with reference to ANSI/SPARC 3 level architecture for DB systems.

if You haven't studied Lessons posted earlier click below Links

Lesson 1,Lesson 2



Lesson 3:

External View(Virtual Tables)

• Level Closest to the user
• Describes DB view of one group of DB users
• Concerned with the way data is viewed by individual users
• Describes part of the DB to users whom it concerns & hide rest of the part from the users

Conceptual View

• Level between External and Internal
• Concern with the way the data is logically represented
• Describes structure of the whole DB for all the users

Internal View

• Level closest to the physical storage
• Concerned with the actual data storage
• Describes physical data storage structure of DB
• Uses physical data model and describes complete access path to DB

One of the main Advantage of using a DB is Data Independence.This concept is explained using 3 level architecture.

Data Independence: Capacity to change the schema at one level of DB system without having to change the schema at the next higher level.

Data Independence is of 2 types

• Logical Data Independence
• Physical Data Independence

Logical Data Independence

1. Capacity to change conceptual schema without changing external schema
2. Can change table structure without changing external view of the table

Physical Data Independence

1. Capacity to change Internal schema without changing conceptual schema
2. File can be reorganize without changing table structures used to create it

Click Here To Download This Lesson in PDF Format

Next Lesson will be based on Normalization.Please keep in touch...

DBMS 1 Lesson 2

By this lesson I'll talk more about DBMS.If you haven't read the Lesson 1 click here.

Lesson 2:

What is DBMS??

DBMS is system software that enables users to define,create,maintain & to provide controlled access to this database.

Subsystems of DBMS is shown below

Functions of DBMS

• Data Security - Limits the access by using passwords.Only authorized people will gain access to database.
• Data Integrity(validity) - Consistency is provided by backup & recovery capabilities.
• Concurrent Data Access - Multiple users can access database at the same time.
• Backup & Recovery - By using automatic software backups are taken.
• User Oriented Data Manipulation - Tools are provided to use query languages(eg: SQL)
• Application Development - Tools are provided for report generation, compilers etc..

Click Here To Download This Lesson in PDF Format

Next Lesson will be based on Database Architecture.So please keep in touch...

DBMS 1 Lesson 1

This is a critical and little bit hard module in 2nd semester of the 1st year; it’s the DBMS 1.

So I thought to blog some lesson series which might help u. 

Lesson 1:

Firstly we’ll start from the basics.
What is a Database? This defines differently in many books. Below explanation is another way of defining.

Database is a shared collection of logically related data designed to meet the information needs of multiple users in an organization. 

Next come the question why to use a database???? We already have file systems(written documents) then why you need to move to more complex computer based systems???

I will show you why database systems are more appropriate than file based traditional systems.

File systems	Database systems
Data Redundancy - lots of data duplication	Data Redundancy is minimal – data is recorded only once
Data is not shareable – little chance of share data outside it’s application	Data shareable – Database is developed to share data
Data Inconsistency – Several versions of same data may exist.	Data consistency – No several versions of same data
No centralized control to handle data	Presence of centralized data control
Data dependence	Data independence
High Program maintenance	Less Program maintenance
Less data Security	High data Security

Though there are many advantages of Database there are disadvantages as well.

• Performance Problems
• Complexity and Size
• Higher cost of DBMS
• Higher impact of Failure
• Additional hardware components need to be installed

Components of a Database System

Click Here To Download This Lesson in PDF Format

Keep in touch for upcoming Lessons..........

Files in C++

Hello reader, today my tutorial is about Files in C++. We use Files in C++ to add values to programme to outer source.

     Imagine we have an outer file called "data.txt" and it has two value 10 and 20. We need to add this value to our c++ programme.

Learn Programming and other computer stuffs

Listen Music While you Browsing!