chapter three objectives identification of keys application of primary and foreign keys converting a...
TRANSCRIPT
![Page 1: Chapter Three Objectives Identification of Keys Application of primary and foreign keys Converting a database design to Relational DB. What is a good DBMS](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649c765503460f9492aa23/html5/thumbnails/1.jpg)
Chapter Three
Objectives Identification of Keys Application of primary and foreign keys Converting a database design to Relational DB.What is a good DBMS
![Page 2: Chapter Three Objectives Identification of Keys Application of primary and foreign keys Converting a database design to Relational DB. What is a good DBMS](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649c765503460f9492aa23/html5/thumbnails/2.jpg)
2
Keys: (Constraints)
A set of attributes whose values uniquely identify each entity in an entity set.
![Page 3: Chapter Three Objectives Identification of Keys Application of primary and foreign keys Converting a database design to Relational DB. What is a good DBMS](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649c765503460f9492aa23/html5/thumbnails/3.jpg)
3
Keys: (Constraints)
Primary key: A key selected by the Database Administrator
Characteristics of primary key:a. Uniqueness:
At any given time, no two tuples can have the same value for a given primary key
Example:
![Page 4: Chapter Three Objectives Identification of Keys Application of primary and foreign keys Converting a database design to Relational DB. What is a good DBMS](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649c765503460f9492aa23/html5/thumbnails/4.jpg)
4
Keys: (Constraints)
b. Minimally:None of the attributes in primary key
can be discarded without distorting the uniqueness property
Example:
![Page 5: Chapter Three Objectives Identification of Keys Application of primary and foreign keys Converting a database design to Relational DB. What is a good DBMS](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649c765503460f9492aa23/html5/thumbnails/5.jpg)
5
Keys: (Constraints)
Foreign key:An attribute(s) in an entity set (relation)
which is the primary key of other entity set (relation)
Example: Department(Name, Dept_Id, ….) Faculty(Name, Id, Dept_Id,…)
![Page 6: Chapter Three Objectives Identification of Keys Application of primary and foreign keys Converting a database design to Relational DB. What is a good DBMS](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649c765503460f9492aa23/html5/thumbnails/6.jpg)
6
Practice:
Identify the primary keys and foreign keys in your video store project.
![Page 7: Chapter Three Objectives Identification of Keys Application of primary and foreign keys Converting a database design to Relational DB. What is a good DBMS](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649c765503460f9492aa23/html5/thumbnails/7.jpg)
7
Normal Forms (Guidelines for RD design)
How do we know this design is good?
If it is not a good design, What should we do?
Modify our design ??.
![Page 8: Chapter Three Objectives Identification of Keys Application of primary and foreign keys Converting a database design to Relational DB. What is a good DBMS](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649c765503460f9492aa23/html5/thumbnails/8.jpg)
8
Normal Forms (Guidelines for RD design)
First Normal Form (1NF) Deals with the shape of the
records
A relation is in 1NF if the values of domain is atomic for each attribute.
![Page 9: Chapter Three Objectives Identification of Keys Application of primary and foreign keys Converting a database design to Relational DB. What is a good DBMS](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649c765503460f9492aa23/html5/thumbnails/9.jpg)
9
First Normal Form: 1NF
Example: R (A, B, C, …)
R ( A B ) R ( A B ) a1 b1, b2 => a1 b1
a1b2
![Page 10: Chapter Three Objectives Identification of Keys Application of primary and foreign keys Converting a database design to Relational DB. What is a good DBMS](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649c765503460f9492aa23/html5/thumbnails/10.jpg)
10
First Normal Form: 1NF
Example: Person (Name Age Children )
Smith 42 John, Lori, Mark
Person (Name Age Children )Smith 42 JohnSmith 42 LoriSmith 42 Mark
![Page 11: Chapter Three Objectives Identification of Keys Application of primary and foreign keys Converting a database design to Relational DB. What is a good DBMS](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649c765503460f9492aa23/html5/thumbnails/11.jpg)
11
First Normal Form: 1NF
Example: Student ( Name Birthday )
S1 Feb 2,91S2 March 8,88
Student (Name, D_Birth, M_Birth, Y_Birth)
Note: 2NF and 3NF Deal with the relationship between non-key and key
![Page 12: Chapter Three Objectives Identification of Keys Application of primary and foreign keys Converting a database design to Relational DB. What is a good DBMS](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649c765503460f9492aa23/html5/thumbnails/12.jpg)
12
Second Normal Form: 2NF
A relation is in 2NF if it is in 1NF and every attribute is Fully dependent on the entire key in this relation.
Fact: 2NF is violated when a non-key is a fact about a subset of a key
![Page 13: Chapter Three Objectives Identification of Keys Application of primary and foreign keys Converting a database design to Relational DB. What is a good DBMS](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649c765503460f9492aa23/html5/thumbnails/13.jpg)
13
Second Normal Form: 2NF
Example: R(A,B,C,D)
D partially depends on A,B C fully depends on A,B A&B are prime (part of key)
If A is primary key. Is this in 2NF? If A&B is primary key. Is this in 2NF?
A, B ---> C, DA ---> D
![Page 14: Chapter Three Objectives Identification of Keys Application of primary and foreign keys Converting a database design to Relational DB. What is a good DBMS](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649c765503460f9492aa23/html5/thumbnails/14.jpg)
14
Second Normal Form: 2NF What should we do with a relation which
is not in 2NF?
Example: R(A,B,C,D) A, B ---> C, D A ---> D
R1 (A,B,C) R2(A,D)
![Page 15: Chapter Three Objectives Identification of Keys Application of primary and foreign keys Converting a database design to Relational DB. What is a good DBMS](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649c765503460f9492aa23/html5/thumbnails/15.jpg)
15
Second Normal Form: 2NF Example: R(Part Warehouse Address Quantity
P1 W1 Frostburg 25
P2 W1 Frostburg 30
P3 W2 Cumberland
32
P4 W4 Frostburg 25
P4 W1What is the primary key?
Part, Warehouse ---> QuantityWarehouse ---> Address
![Page 16: Chapter Three Objectives Identification of Keys Application of primary and foreign keys Converting a database design to Relational DB. What is a good DBMS](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649c765503460f9492aa23/html5/thumbnails/16.jpg)
16
Second Normal Form: 2NF
Problems:1. Repetition of information:
Changing the address W!
2. Unable to present information:Warehouse with no part
So …R1 (Warehouse, Address)R2 (Part, Warehouse, Quantity)
![Page 17: Chapter Three Objectives Identification of Keys Application of primary and foreign keys Converting a database design to Relational DB. What is a good DBMS](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649c765503460f9492aa23/html5/thumbnails/17.jpg)
17
Second Normal Form: 2NF
Example:
R( Professor,
Student,
Course,
Degree )
P1 S1 C1 Ph.D.
P2 S2 C2 M.S.
P3 S2 C4 M.S.
P4 S3 C4 Pg.D.Professor ---> CourseStudent ---> DegreeProfessor ---> Student
Key? Not in 2NF
R1(Student, Degree)R2(Professor, Course, Student)
![Page 18: Chapter Three Objectives Identification of Keys Application of primary and foreign keys Converting a database design to Relational DB. What is a good DBMS](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649c765503460f9492aa23/html5/thumbnails/18.jpg)
18
A relation is 3NF if it is in 2NF and every non-key attribute non transitively depends on the Primary Key.
Example: R(A,B,C,D) A, B --->D D ---> C
Fact: 3NF is violated when a non-key is a fact about another non-key
Third Normal Form (3NF):
R1(A,B,D)R2(D,C)
![Page 19: Chapter Three Objectives Identification of Keys Application of primary and foreign keys Converting a database design to Relational DB. What is a good DBMS](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649c765503460f9492aa23/html5/thumbnails/19.jpg)
19
Third Normal Form (3NF): Example: R(Employee, Dept, Location) Employee ---> Dept
Dept ---> Location
Employee Dept LocationE1 D1 Frostburg
E2 D2 Frostburg
E3 D3 Frostburg
Problems? R1(Employee, Dept)R2(Dept, Location)
![Page 20: Chapter Three Objectives Identification of Keys Application of primary and foreign keys Converting a database design to Relational DB. What is a good DBMS](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649c765503460f9492aa23/html5/thumbnails/20.jpg)
20
Third Normal Form (3NF): Example: R(A,B,C,D)
A,B ---> C A,C ---> D
So A,B is the Primary Key Not in 3NF
R1(A,B,C) R2(A,C,D)
![Page 21: Chapter Three Objectives Identification of Keys Application of primary and foreign keys Converting a database design to Relational DB. What is a good DBMS](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649c765503460f9492aa23/html5/thumbnails/21.jpg)
21
Practice:
Check the relations in your video project for 1NF, 2NF and 3NF. If they are not in normal forms, redesign your database model.