MIT1_204S10_lec02

MIT1_204S10_lec02 - 1.204 Lecture 2 Data models, concluded...

Info iconThis preview shows pages 1–6. Sign up to view the full content.

View Full Document Right Arrow Icon
1 1.204 Lecture 2 Data models, concluded Nl Normalizati t i on Keys Primary Primary key key: one one or or more more attributes attributes that that uniquely uniquely identify a record Name or identifying number, often system generated Composite keys are made up of two fields E.g., aircraft manufacturer and model number
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
2 Foreign keys Primary Primary key key of of the the independent independent or parent or parent entity type is maintained as a non-key attribute in the dependent or child entity type Foreign keys DeptID DeptName 930 Receiving 378 Assembly 372 Finance 923 Pl i EmpID DeptID EmpFirstName EmpLastName 4436 483 Brown John 4574 483 Jones Helen 5678 372 Smith Jane 5674 372 Crane Sally 923 Planning 483 Construction 9987 923 Black Joe 5123 923 Green Bill 5325 483 Clinton Bob Database requires a valid department number when employee is added Employee ID is the unique identifier of employees; department number is not needed as part of the employee primary key
Background image of page 2
3 Composite foreign keys Independent/parent Dependent/child (must contain, as (must contain as a foreign key, the primary key of the independent entity) Assume a charter airline: every flight has a different number What has to change if this is a scheduled carrier? Composite foreign keys Flight FlightNbr FlightDate DepartTime ArrivalTime 243 9/24/00 9:00am 11:00am 253 9/24/00 10 00 12 30 FlightSeat FlightNbr SeatNbr SeatStatus SeatDescription 243 8A Confirmed Window 243 7D Reserved Aisle 9/24/00 10:00am 12:30pm 52 9/24/00 11:00am 2:00pm 243 14E Open Center 253 1F Open Window 253 43A Confirmed Window Flight number must be part of the flight seat primary key; this is different than employee and department, where department is not required.
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
4 Foreign keys (many-many relationships) Primary y key of p arent is used in primary key of child Independent Dependent Independent Vehicle can be driven by many drivers; driver can drive many vehicles Many-to-many relationships with foreign keys Vehicle VehicleID VehicleMake VehicleModel Vehicle Driver VehicleID DriverID Driver DriverID DriverName DriverLicenseNbr 35 Volvo Wagon 33 Ford Sedan 89 GMC Truck 35 900 35 253 89 900 253 Ken A23423 900 Jen B89987 Never create an entity with vehicle1, vehicle2,… !
Background image of page 4
5 Five normal forms: preventing errors 1: 1: All All occurrences ccurrences of of an an entity entity must must contain contain the he same number of attributes. No lists, no repeated attributes. 2: All non- primary key fields must be a function of the primary key. 3: All non- primary key fields must not be a function of other non- primary key fields. 4: A row must not contain two or more independent multi-valued facts about an entity.
Background image of page 5

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Image of page 6
This is the end of the preview. Sign up to access the rest of the document.

Page1 / 13

MIT1_204S10_lec02 - 1.204 Lecture 2 Data models, concluded...

This preview shows document pages 1 - 6. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online