190 likes | 313 Views
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
E N D
Database Design ConceptsINFO1408 • 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 • Student ( student#, name) • Student# is the primary key- this will uniquely identify a student- no two students will have the same student#
Breaking that rule The new (last)row in the table does not make sense- the student# is not unique
Functional Determinancy • For every Student# there is only one possible name ( not necessarily unique) • We say student# determines name Student# name
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#
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
Can you draw the determinancy diagram for the following tables 1. Car ( registration#, make, model, colour)
Answer Registration # make model colour
How would you draw this determinancy diagram Does man determine date? Does woman determine date? Assume this is about wedding dates
How about this ? In this case the primary key is a composite key Man# date Woman #
Example • Draw determinancy diagram • Table type For this table occurrence
AnswerTable Type order (customer#,part#,qtyordered) Customer # Quantity ordered Part#
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
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.
Using determinants to remove redundancy- What Determinants can you see here? • This table contains redundancy
Shop id determines Manager and area-codeArea-code determines location Shop-id manager Area Code Location
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