Relational database systems
Download
1 / 63

Relational Database Systems - PowerPoint PPT Presentation


  • 320 Views
  • Updated 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 l.jpg

Relational Database Systems

Higher Information Systems


The relational model l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
Entity-Occurrence Modelling

  • This method isonly as good asthe available data

  • Make up “dummy”data if necessary tofill in the gaps


More about keys l.jpg
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 l.jpg
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 l.jpg
The flat file revisited…

  • What is a suitable key?

    • DVD Code?

    • Member Number?

    • (DVD Code, Member Number)?


Update anomalies l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
Un-normalised Form (UNF)

  • Identify an entity

  • List all the attributes

  • Identify a key


Un normalised form unf16 l.jpg
Un-normalised Form (UNF)

  • Identify repeating data items


Un normalised form unf17 l.jpg
Un-normalised Form (UNF)

  • Identify repeating data items


First normal form 1nf l.jpg
First Normal Form (1NF)

  • Remove repeating data items to form a new entity

  • Take the key with you!


First normal form 1nf19 l.jpg
First Normal Form (1NF)

  • Remove repeating data items to form a new entity

  • Take the key with you!


First normal form 1nf20 l.jpg
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 l.jpg
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 l.jpg
First Normal Form (1NF)

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



First normal form 1nf24 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
Second Normal Form (2NF)

  • But there are still update anomalies with the Order entity


Third normal form 3nf l.jpg
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 l.jpg
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 l.jpg
Third Normal Form (3NF)

  • Remove these attributes to a new entity


Third normal form 3nf35 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
Memory Aid

  • In 3NF, each attribute is dependent on

  • the key

  • the whole key

  • and nothing but the key



Entity relationship diagram40 l.jpg
Entity-Relationship Diagram

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



Source documents42 l.jpg
Source documents

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


Source documents43 l.jpg
Source documents

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

  • Identify a key


Source documents44 l.jpg
Source documents

  • There are two attributes called Title


Source documents45 l.jpg
Source documents

  • There are two attributes called Title

  • Member Number is the same as Member


Source documents46 l.jpg
Source documents

  • There are two attributes called Title

  • Member Number is the same as Member

  • Number or No?


Source documents47 l.jpg
Source documents

  • Tidy up UNF

  • Carry on as before to 3NF


Database design l.jpg
Database Design

  • For each attribute you must decide

    • its name

    • its data type

    • its properties


Database design49 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
Database Design

  • Names are usually stored as 3 or 4 fields

    • Title (Mr/Mrs/Miss/Ms)

    • Forename

    • Initials/Other Names

    • Surname


Database design55 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
Database Design

  • For each attribute you must decide

    • its name

    • its data type

    • its properties

  • Store this information in a Data Dictionary







ad