1 / 19

Database Design Concepts INFO1408

Database Design Concepts INFO1408. Lecture 17 ( term 2 week 6) Primary Keys and functional determinancy. Objectives. Introduce the concepts of Primary Key Introduction to determinancy diagrams. Keys Within the relational data model. The Primary Key = Unique Identifier Example

aileen-pierce
Download Presentation

Database Design Concepts INFO1408

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Database Design ConceptsINFO1408 • Lecture 17 ( term 2 week 6) • Primary Keys and functional determinancy

  2. Objectives • Introduce the concepts of • Primary Key • Introduction to determinancy diagrams

  3. Keys Within the relational data model • The Primary Key = Unique Identifier • Example • Student ( student#, name) • Student# is the primary key- this will uniquely identify a student- no two students will have the same student#

  4. Breaking that rule The new (last)row in the table does not make sense- the student# is not unique

  5. Functional Determinancy • For every Student# there is only one possible name ( not necessarily unique) • We say student# determines name Student# name

  6. If another student is added to the table with the same name as long as the student# is different it is OKThe name does not determine student#

  7. An example of a determinancy diagram for a tableCustomer( customer#, name, address, postcode, tel#) name customer# Note Primary key determines all attributes in a table address Tel # postcode

  8. Can you draw the determinancy diagram for the following tables 1. Car ( registration#, make, model, colour)

  9. Answer Registration # make model colour

  10. How would you draw this determinancy diagram Does man determine date? Does woman determine date? Assume this is about wedding dates

  11. How about this ? In this case the primary key is a composite key Man# date Woman #

  12. Example • Draw determinancy diagram • Table type For this table occurrence

  13. AnswerTable Type order (customer#,part#,qtyordered) Customer # Quantity ordered Part#

  14. We can show a chain of determinants Order# determines order date and cust# Cust# determines cust-name What are the table types for this ? Order# Order-date Cust# Cust-name

  15. Table types • Order(order#, order-date, cust#) • Customer(Cust#, cust-name) • Cust# is in both tables. It is a foreign key in the order table which is shown in bold.

  16. Using determinants to remove redundancy- What Determinants can you see here? • This table contains redundancy

  17. Shop id determines Manager and area-codeArea-code determines location Shop-id manager Area Code Location

  18. Non redundant table occurences are

  19. Summary • By identifying the determinant ( or primary key) it is possible to remove redundancy and to create normalised tables • We will look next week at carrying out the normalisation process

More Related