1 / 26

Database Management Systems

Database Management Systems. Session 5. Objectives. Understand the structure of a data table Understand data normalisation Understand and use the terms: - entity - attribute - field - record - primary key Understand the principle of linking tables in a relational database. Database Tables

elina
Download Presentation

Database Management Systems

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 Management Systems Session 5

  2. Objectives • Understand the structure of a data table • Understand data normalisation • Understand and use the terms: - entity- attribute- field- record- primary key • Understand the principle of linking tables in a relational database

  3. Database Tables Normalisation Linking Tables Database Schema Topics

  4. Record Gertrude Jones Name Field Address 34 Great West RoadLondon W23 8RT 020 8564 1234 020 8564 1235 Tel FAX 07814 100200 Data Mobile gertie@somecompany.co.uk Email Address Book

  5. Contacts Table Field Record

  6. Entities and Attributes • “I want to record the names and addresses of my contacts along with their phone numbers, FAX numbers, mobile numbers and their email addresses” • Entity: Contact • Attributes of Contact:- Name- Address- Phone- FAX- Mobile- Email

  7. Ordered Collection of Data • Characteristics:- each Record is unique- repetition of data is avoided • Database Tables follow certain rules called:- First Normal Form- Second Normal Form- Third Normal Form

  8. Primary Key • A Field (or fields) which has a unique value for each record • Eg. - National Insurance number- Car registration number- Passport number

  9. First Normal Form • Data is atomic, self-contained and independent • No field in a table contains any repeating groups • No record in a table contains repeating groups

  10. Second Normal Form • All the non-key columns must be dependent on the entire primary key

  11. Third Normal Form • All the non-key columns of the table must be only dependent on the primary key and not on each other

  12. Identifying Entities and Relationships • Read the scenario and pick out the nouns • Pick out which nouns are giving information about another noun eg Name is telling you additional information about Contact • In this case Contact is the Entity and Name is an Attribute of that Entity

  13. Exercise: Entities and Attributes • Pick out the database entities and suggest useful attributes: • A library has about 300 borrowers who complete a registration form when they join. There are about 25,000 books available for loan. A loan is for 2 weeks and may be renewed over the phone. Overdue books are charged at 10p per day. If a book is out on loan, another borrower may request it.

  14. Text Data • For example the Name field • The size of the field is determined by the maximum number of characters you want to store • The size of the field is the same for each record so empty spaces waste storage space

  15. Number Data • For Example in the Salary field • The size of the field is determined by the maximum precision of the numbers you want to store • Integer • Long Integer • Single • Double

  16. Date Data • Stored on the computer as a number • Different Formats eg:Short date – 01/02/06

  17. Logical Data • Otherwise known as ‘Yes/No’ • Takes up minimal storage space

  18. Yellow 22 3 Apple A plum 22 3 A plum Apple Yellow Sorting on Text Data Sort Ascending

  19. Points to Consider • What type of data is a telephone number? • How can you name files on a Windows system with digits so that they sort correctly?

  20. Purchase Purchase ID Customer ID Date Item Amount Customer Customer ID Name Address Phone Credit Limit PK PK Linking on the Primary Key

  21. Linked Fields Linked Fields One to Many Relationship

  22. Most Common Types of Relationship Staff Purchase Customer Employee Contractor One to ManyWhere one record in one tablelinks with many in the other table One to OneWhere one record in one tablelinks with one in the other table

  23. Borrower Book Many to Many LinkA borrower can borrow many books and a book may be borrowed by many borrowers – over a period of time Many to Many Relationship

  24. Resolving Many to Many Relationships Many to Many Links are impossible to programme into a database system.This type of relationship may be resolved into 2 many to one relationships. Book Borrower Loan

  25. Scenario • Draw an E-R diagram: • Northwind is a wholesaler of delicatessen products. Customers are all over the world and each member of the sales team may deal with any customer. Products is kept in a central warehouse and items are ordered from Suppliers when numbers reach the re-order level. An Order may consist of several different products and may be delivered by any one of three Shippers.

  26. Entity-Relationship Diagram

More Related