Relational Database Systems

1
Relational Database Systems

• Higher Information Systems

2
The Relational Model

• data is grouped into entities which are related,
  in order to minimise data duplication and achieve
  data integrity
• many-to-many relationships between entities are
  removed and replaced with one-to-many
  relationships

3
Entity-Occurrence Modelling
4
Entity-Occurrence Modelling

• Lines indicate howthe instances ofeach entity
  arelinked
• E.g. Member 1034 has rented DVDs 002 and 015
• DVD 003 has been rented by members 1012 1056

5
Entity-Occurrence Modelling

• Each DVD can berented by manyMembers
• Each Member canrent many DVDs
• So there is a many-to-many relationship between
  Member and DVD

6
Entity-Occurrence Modelling

• This method isonly as good asthe available data
• Make up dummydata if necessary tofill in the
  gaps

7
More about keys

• An atomic key consists of one attribute
• MEMBER(Member Number, Name, Telephone Number)
• A compound key consists of two or more attributes
• MEMBER(Member Number, Name, Telephone Number)
• A surrogate key is a made up attribute designed
  to identify a record
• Member Number is a surrogate key

8
Choosing a key

• An atomic key is better than a compound key
• A numeric attribute is better than a text
  attribute
• KISS Keep It Short and Simple
• A key must have a valueit cannot be blank (or
  null)
• A key should not change over time

9
The flat file revisited

• What is a suitable key?
• DVD Code?
• Member Number?
• (DVD Code, Member Number)?

10
Update Anomalies

• There is no way of storing the details of a
  member who hasnt rented any DVDs
• A value must be provided for both DVD Code and
  Member Number for the key
• This is called an insertion anomaly

11
Update Anomalies

• If a members details have to be amended, this
  must be done in each record with those details
• This can lead to data inconsistency if there is
  an error or omission in making the change
• This is called a modification anomaly

12
Update Anomalies

• If a DVD is removed from the database, then it
  may also remove the only record of a members
  details
• This is called a deletion anomaly

13
Update Anomalies

• Insertion anomalies
• Modification anomalies
• Deletion anomalies
• These are characteristics of poorly designed
  databases
• The solution is to use a relational database
• We use normalisation to help work out what tables
  are required and which data items should be
  stored in each table

14
Normalisation
15
Un-normalised Form (UNF)

• Identify an entity
• List all the attributes
• Identify a key

16
Un-normalised Form (UNF)

• Identify repeating data items

17
Un-normalised Form (UNF)

• Identify repeating data items

18
First Normal Form (1NF)

• Remove repeating data items to form a new entity
• Take the key with you!

19
First Normal Form (1NF)

• Remove repeating data items to form a new entity
• Take the key with you!

20
First Normal Form (1NF)

• Identify a key for the new entity
• It will be a compound key
• Use the original key and add to it

21
First Normal Form (1NF)

• Identify a key for the new entity
• It will be a compound key
• Use the original key and add to it
• Label the foreign key
• Order Number is both part of the compound primary
  key and also a foreign key.

22
First Normal Form (1NF)

• A data model is in 1NF if it has no multi-valued
  attributes

23
First Normal Form (1NF)
24
First Normal Form (1NF)

• But what if there were lots of orders for large
  deluxe red widgets?
• There are still update anomalies

25
Second Normal Form (2NF)

• Examine any entity with a compound key (in this
  case ORDER_ITEM)
• See if any attributes are dependent on just one
  part of the compound key
• These are called partial dependencies

26
Second Normal Form (2NF)

• Order Number is part of the key
• Item Code is part of the key
• Description is dependent on the Item Code
• Unit Cost is dependent on the Item Code
• Quantity is dependent on both Order Number and
  Item Code.

27
Second Normal Form (2NF)

• Description and Unit Cost are partial
  dependencies
• They are dependent on Item Code
• Remove these attributes to a new entity
• Take a copy of the attribute they are dependent on

28
Second Normal Form (2NF)

• Item Code becomes the key of the new entity
• And becomes a foreign key in ORDER-ITEM

29
Second Normal Form (2NF)

• A data model is in 2NF if it is in 1NF and there
  are no partial dependencies

30
Second Normal Form (2NF)

• We can add an item to the Item table without it
  having to be on an order
• We can delete an order in the Order table without
  deleting details of the items on the order
• We can update item details once in the Item table
  without affecting the orders for that item in the
  Order-Item table

31
Second Normal Form (2NF)

• But there are still update anomalies with the
  Order entity

32
Third Normal Form (3NF)

• Examine all the entities produced so far
• See if there are any non-key attributes which are
  dependent on any other non-key attributes
• These are called non-key dependencies

33
Third Normal Form (3NF)

• In the ORDER entity, Customer Name, Address, Post
  Code and Telephone Number are all dependent on
  Customer Number

34
Third Normal Form (3NF)

• Remove these attributes to a new entity

35
Third Normal Form (3NF)

• Remove these attributes to a new entity
• Customer Number is the key of the new entity
• Leave Customer Number behind as a foreign key

36
Third Normal Form (3NF)

• A data model is in 3NF if it is in 2NF and there
  are no non-key dependencies

37
Third Normal Form (3NF)

• We can add a customer to the Customer table
  without the customer having to place an order
• We can delete an order in the Order table without
  deleting details of the customer who placed the
  order
• We can update a customers details once in the
  Customer table without affecting the orders
  placed by that customer in the Order table

38
Memory Aid

• In 3NF, each attribute is dependent on
• the key
• the whole key
• and nothing but the key

39
Entity-Relationship Diagram
40
Entity-Relationship Diagram

• The foreign key is always at the many end of
  the relationship

41
Source documents
42
Source documents

• List all the attributes which must be stored in
  the database

43
Source documents

• List all the attributes which must be stored in
  the database
• Identify a key

44
Source documents

• There are two attributes called Title

45
Source documents

• There are two attributes called Title
• Member Number is the same as Member

46
Source documents

• There are two attributes called Title
• Member Number is the same as Member
• Number or No?

47
Source documents

• Tidy up UNF
• Carry on as before to 3NF

48
Database Design

• For each attribute you must decide
• its name
• its data type
• its properties

49
Database Design

• For each attribute you must decide
• its name
• Choose sensible and meaningful field names
• Be consistent!
• e.g. Number/Num/No/

50
Database Design

• For each attribute you must decide
• its name
• its data type
• text (alphanumeric, string)
• numeric (integer, real, currency)
• date or time
• Boolean (yes or no)
• link
• object (e.g. picture, sound, file)

51
Data Types

• Text
• Smith
• John Smith
• Alphanumeric
• IV99 9ZZ
• 01234 567890
• 10 Downing Street
• 10
• Free text The cat sat on the mat, etc

52
Data Types

• Numeric
• Integer 3, 1246, 0, -5
• Real/floating point 3.14, 1246.0, 0, -5.2
• Currency 3.14, 1246.00, 0.00, -5.20
• Note that the currency symbol is not stored

53
Data Types

• Date
• Short date 1/1/2006
• Long date 29 February 2004
• Medium date 29 Feb 2004
• dd/mm/yyyy indicates format
• Watch out for US dates mm/dd/yyyy

54
Database Design

• Names are usually stored as 3 or 4 fields
• Title (Mr/Mrs/Miss/Ms)
• Forename
• Initials/Other Names
• Surname

55
Database Design

• Addresses are usually stored as 3 or 4 fields
• Address1 (Street Address)
• Address2 (Town)
• Address3 (District)
• Post Code
• Sometimes the house number is stored separately
  from the Street Name

56
Database Design

• Telephone Numbers are always text
• Numbers are usually text if they are not used in
  calculations, e.g. House Number
• Other numbers are also stored as text
• ISBNs
• Vehicle Registration numbers
• Use integers for whole numbers

57
Database Design

• For each attribute you must decide
• its name
• its data type
• its properties
• Primary key/foreign key PK/FK
• Validation (presence, range, restricted choice)
• Default value
• Format

58
Database Design

• For each attribute you must decide
• its name
• its data type
• its properties
• Store this information in a Data Dictionary

59
Data Dictionary
60
Data Dictionary
61
Data Dictionary
62
Data Dictionary
63
Data Dictionary