the relational model cis 218. entity a person, place, thing or transaction something the user wants...
Post on 20-Dec-2015
222 views
TRANSCRIPT
![Page 1: The Relational Model CIS 218. Entity A Person, Place, Thing or Transaction Something the user wants to track](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649d4b5503460f94a280cf/html5/thumbnails/1.jpg)
The Relational Model
CIS 218
![Page 2: The Relational Model CIS 218. Entity A Person, Place, Thing or Transaction Something the user wants to track](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649d4b5503460f94a280cf/html5/thumbnails/2.jpg)
Entity
• A Person, Place, Thing or Transaction
• Something the user wants to track
![Page 3: The Relational Model CIS 218. Entity A Person, Place, Thing or Transaction Something the user wants to track](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649d4b5503460f94a280cf/html5/thumbnails/3.jpg)
Attribute
• A Property that describes an entity
• Also called a Field
Title
Author
Number of Pages
Subject
???
![Page 4: The Relational Model CIS 218. Entity A Person, Place, Thing or Transaction Something the user wants to track](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649d4b5503460f94a280cf/html5/thumbnails/4.jpg)
Attribute
• A Property that describes an entity
![Page 5: The Relational Model CIS 218. Entity A Person, Place, Thing or Transaction Something the user wants to track](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649d4b5503460f94a280cf/html5/thumbnails/5.jpg)
Identifier
• An attribute that names, or identifies, an entity instance
• May or may not be unique
Ludwig
![Page 6: The Relational Model CIS 218. Entity A Person, Place, Thing or Transaction Something the user wants to track](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649d4b5503460f94a280cf/html5/thumbnails/6.jpg)
Primary Key
• A unique identifier
![Page 7: The Relational Model CIS 218. Entity A Person, Place, Thing or Transaction Something the user wants to track](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649d4b5503460f94a280cf/html5/thumbnails/7.jpg)
Relation
• A relation is a two-dimensional table that has specific characteristics
2-7
![Page 8: The Relational Model CIS 218. Entity A Person, Place, Thing or Transaction Something the user wants to track](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649d4b5503460f94a280cf/html5/thumbnails/8.jpg)
Characteristics of a Relation
• Consists of columns and rows• Each row contains data about one entity• Each columns contains data about one attribute
of the entity• All entries in a column are of the same kind• Each cell of the table holds a single value• Each column has a unique name• No two rows may be identical
2-8
![Page 9: The Relational Model CIS 218. Entity A Person, Place, Thing or Transaction Something the user wants to track](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649d4b5503460f94a280cf/html5/thumbnails/9.jpg)
Is this a Relation?
2-9
EmployeeNumber Phone LastName
100 335-6421
454-9744
Abernathy
101 215-7789 Cadley
104 610-9850 Copley
107 299-9090 Jackson
![Page 10: The Relational Model CIS 218. Entity A Person, Place, Thing or Transaction Something the user wants to track](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649d4b5503460f94a280cf/html5/thumbnails/10.jpg)
Is this a Relation?
2-10
EmployeeNumber Phone LastName
100 335-6421 Abernathy
101 215-7789 Cadley
104 610-9850 Copley
100 335-6421 Abernathy
107 299-9090 Jackson
![Page 11: The Relational Model CIS 218. Entity A Person, Place, Thing or Transaction Something the user wants to track](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649d4b5503460f94a280cf/html5/thumbnails/11.jpg)
Is this a Relation?
2-11
EmployeeNumber
Phone Name Name
100 335-6421 Robin Abernathy
101 215-7789 Jessica Cadley
104 610-9850 Roger Copley
105 222-8873 Gurpreet Kaur
107 299-9090 Jazmyne Jackson
![Page 12: The Relational Model CIS 218. Entity A Person, Place, Thing or Transaction Something the user wants to track](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649d4b5503460f94a280cf/html5/thumbnails/12.jpg)
Is this a Relation?
2-12
EmployeeNumber
Phone Name Number
100 335-6421 Robin 123
101 215-7789 Jessica $27.45
104 610-9850 Roger Fourteen
105 222-8873 Gurpreet 37
107 299-9090 Jazmyne 5-06-65
![Page 13: The Relational Model CIS 218. Entity A Person, Place, Thing or Transaction Something the user wants to track](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649d4b5503460f94a280cf/html5/thumbnails/13.jpg)
Is this a Relation?
2-13
EmployeeNumber FirstName LastName
100 Mary Abernathy
101 Jerry Cadley
104 Alex Copley
107 Megan Jackson
![Page 14: The Relational Model CIS 218. Entity A Person, Place, Thing or Transaction Something the user wants to track](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649d4b5503460f94a280cf/html5/thumbnails/14.jpg)
A Composite Key
• A primary key made up of two or more attributes (columns)
• For a key to be unique, often it must become a composite key– Example: How would I look up a grade?
2-14
![Page 15: The Relational Model CIS 218. Entity A Person, Place, Thing or Transaction Something the user wants to track](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649d4b5503460f94a280cf/html5/thumbnails/15.jpg)
A Candidate Key
• A candidate to become the primary key• A unique identifier
– SID, SSN
2-15
![Page 16: The Relational Model CIS 218. Entity A Person, Place, Thing or Transaction Something the user wants to track](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649d4b5503460f94a280cf/html5/thumbnails/16.jpg)
A Surrogate Key
• A unique, numeric value that is added to a relation to serve as the Primary Key
• Has no meaning to users • Usually hidden in forms, queries and reports• In Access, uses an AutoNumber
2-16
![Page 17: The Relational Model CIS 218. Entity A Person, Place, Thing or Transaction Something the user wants to track](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649d4b5503460f94a280cf/html5/thumbnails/17.jpg)
Relationships
• Associations between entities
![Page 18: The Relational Model CIS 218. Entity A Person, Place, Thing or Transaction Something the user wants to track](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649d4b5503460f94a280cf/html5/thumbnails/18.jpg)
Relationships Between Tables
• A table may be related to other tables– An Employee is assigned to a Car– A Student takes a Class
• To relate tables, create a foreign key– Also called a linking column
• A foreign key is a primary key from one table placed into another table
2-18
![Page 19: The Relational Model CIS 218. Entity A Person, Place, Thing or Transaction Something the user wants to track](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649d4b5503460f94a280cf/html5/thumbnails/19.jpg)
Foreign Key Example
2-19
Project
ProjID
ProjName
MgrID
Manager
MgrID
MgrName
Foreign Key
![Page 20: The Relational Model CIS 218. Entity A Person, Place, Thing or Transaction Something the user wants to track](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649d4b5503460f94a280cf/html5/thumbnails/20.jpg)
Foreign Key Example
2-20
Department
DeptID
DeptName
Location
Employee
EmpID
DeptID
EmpName
Foreign Key
![Page 21: The Relational Model CIS 218. Entity A Person, Place, Thing or Transaction Something the user wants to track](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649d4b5503460f94a280cf/html5/thumbnails/21.jpg)
Foreign Key Example
2-21
Song
SongID
SongName
Time
Artist
ArtistID
ArtistName
Foreign KeyArtistID
![Page 22: The Relational Model CIS 218. Entity A Person, Place, Thing or Transaction Something the user wants to track](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649d4b5503460f94a280cf/html5/thumbnails/22.jpg)
Referential Integrity
• Every foreign key value must match a value of an existing primary key
• Therefore, the primary key value must exist before the foreign key value is entered
2-22
![Page 23: The Relational Model CIS 218. Entity A Person, Place, Thing or Transaction Something the user wants to track](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649d4b5503460f94a280cf/html5/thumbnails/23.jpg)
2-23
![Page 24: The Relational Model CIS 218. Entity A Person, Place, Thing or Transaction Something the user wants to track](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649d4b5503460f94a280cf/html5/thumbnails/24.jpg)
Referential Integrity
Each value of the Foreign Key OwnerID in PET
must exist in
The values of the Primary Key OwnerID in OWNER
2-24
![Page 25: The Relational Model CIS 218. Entity A Person, Place, Thing or Transaction Something the user wants to track](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649d4b5503460f94a280cf/html5/thumbnails/25.jpg)
Foreign Keys inMicrosoft Access
2-25