relational database systems
Download
Skip this Video
Download Presentation
Relational Database Systems

Loading in 2 Seconds...

play fullscreen
1 / 63

Relational Database Systems - PowerPoint PPT Presentation


  • 320 Views
  • Uploaded on

Relational Database Systems. Higher Information Systems. 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.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Relational Database Systems' - Rita


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
relational database systems

Relational Database Systems

Higher Information Systems

the relational model
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
entity occurrence modelling4
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
entity occurrence modelling5
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
entity occurrence modelling6
Entity-Occurrence Modelling
  • This method isonly as good asthe available data
  • Make up “dummy”data if necessary tofill in the gaps
more about keys
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
choosing a key
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 value—it cannot be blank (or “null”)
  • A key should not change over time
the flat file revisited
The flat file revisited…
  • What is a suitable key?
    • DVD Code?
    • Member Number?
    • (DVD Code, Member Number)?
update anomalies
Update Anomalies
  • There is no way of storing the details of a member who hasn’t rented any DVDs
  • A value must be provided for both DVD Code and Member Number for the key
  • This is called an “insertion anomaly”
update anomalies11
Update Anomalies
  • If a member’s 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”
update anomalies12
Update Anomalies
  • If a DVD is removed from the database, then it may also remove the only record of a member’s details
  • This is called a “deletion anomaly”
update anomalies13
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
un normalised form unf
Un-normalised Form (UNF)
  • Identify an entity
  • List all the attributes
  • Identify a key
un normalised form unf16
Un-normalised Form (UNF)
  • Identify repeating data items
un normalised form unf17
Un-normalised Form (UNF)
  • Identify repeating data items
first normal form 1nf
First Normal Form (1NF)
  • Remove repeating data items to form a new entity
  • Take the key with you!
first normal form 1nf19
First Normal Form (1NF)
  • Remove repeating data items to form a new entity
  • Take the key with you!
first normal form 1nf20
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
first normal form 1nf21
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.
first normal form 1nf22
First Normal Form (1NF)
  • A data model is in 1NF if it has no multi-valued attributes
first normal form 1nf24
First Normal Form (1NF)
  • But what if there were lots of orders for large deluxe red widgets…?
  • There are still update anomalies
second normal form 2nf
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
second normal form 2nf26
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.
second normal form 2nf27
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
second normal form 2nf28
Second Normal Form (2NF)
  • Item Code becomes the key of the new entity
  • And becomes a foreign key in ORDER-ITEM
second normal form 2nf29
Second Normal Form (2NF)
  • A data model is in 2NF if it is in 1NF and there are no partial dependencies
second normal form 2nf30
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
second normal form 2nf31
Second Normal Form (2NF)
  • But there are still update anomalies with the Order entity
third normal form 3nf
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
third normal form 3nf33
Third Normal Form (3NF)
  • In the ORDER entity, Customer Name, Address, Post Code and Telephone Number are all dependent on Customer Number
third normal form 3nf34
Third Normal Form (3NF)
  • Remove these attributes to a new entity
third normal form 3nf35
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
third normal form 3nf36
Third Normal Form (3NF)
  • A data model is in 3NF if it is in 2NF and there are no non-key dependencies
third normal form 3nf37
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 customer’s details once in the Customer table without affecting the orders placed by that customer in the Order table
memory aid
Memory Aid
  • In 3NF, each attribute is dependent on
  • the key
  • the whole key
  • and nothing but the key
entity relationship diagram40
Entity-Relationship Diagram
  • The foreign key is always at the “many” end of the relationship
source documents42
Source documents
  • List all the attributes which must be stored in the database
source documents43
Source documents
  • List all the attributes which must be stored in the database
  • Identify a key
source documents44
Source documents
  • There are two attributes called Title
source documents45
Source documents
  • There are two attributes called Title
  • Member Number is the same as Member
source documents46
Source documents
  • There are two attributes called Title
  • Member Number is the same as Member
  • Number or No?
source documents47
Source documents
  • Tidy up UNF
  • Carry on as before to 3NF
database design
Database Design
  • For each attribute you must decide
    • its name
    • its data type
    • its properties
database design49
Database Design
  • For each attribute you must decide
    • its name
      • Choose sensible and meaningful field names
      • Be consistent!
      • e.g. Number/Num/No/#
database design50
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)
data types
Data Types
  • Text
    • “Smith”
    • “John Smith”
    • Alphanumeric
      • “IV99 9ZZ”
      • “01234 567890”
      • “10 Downing Street”
      • “10”
    • Free text: “The cat sat on the mat, etc…”
data types52
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
data types53
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
database design54
Database Design
  • Names are usually stored as 3 or 4 fields
    • Title (Mr/Mrs/Miss/Ms)
    • Forename
    • Initials/Other Names
    • Surname
database design55
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
database design56
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
database design57
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
database design58
Database Design
  • For each attribute you must decide
    • its name
    • its data type
    • its properties
  • Store this information in a Data Dictionary
ad