1 / 23

A2 Computing

A2 Computing. Lesson Databases - Introduction. Aims and Objectives. To understand what a relational database is To understand how to design a relational database using ER modelling To be able to construct a model of a new database using this technique. What is a database?.

hisa
Télécharger la présentation

A2 Computing

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. A2 Computing Lesson Databases - Introduction

  2. Aims and Objectives • To understand what a relational database is • To understand how to design a relational database using ER modelling • To be able to construct a model of a new database using this technique

  3. What is a database? • A structured collection of data • Data is usually stored in tables Surname: James First name: Samantha Date of Birth: 20.12.89 Address: 16 Poppy Close Town: Southam County: Warwickshire Surname: Brown First name: David Date of Birth: 20.12.89 Address: 16 Poppy Close Town: Southam County: Warwickshire Surname: Lastler First name: David Date of Birth: 20.12.89 Address: 16 Poppy Close Town: Southam County: Warwickshire Surname: Whitefield First name: Nina Date of Birth: 21.02.76 Address: 16 Poppy Close Town: Southam County: Warwickshire

  4. Flat Files A flat file is a database held in a single file consisting of one table. Record Field File

  5. Flat File problems • There are a number of problems using flat files where everything is stored in one table • data is often repeated in several different places • as a result, updating of data is time consuming and often inaccurate • data entry is time consuming and prone to errors

  6. Using multiple tables • Using a database with multiple tables (a relational database) means that data can be separated into a more structured design • Data between tables is linked so that it is accessed only when required ( a relationship is established) • Advantages • Reduces the repeating of data (redundant data) • Reduces error on updating and inputting of data • Increases the reliability of the database (data integrity) • Increases speed of data retrieval

  7. Building a database • Before a relational database can be built it needs to be designed so it works effectively and efficiently • The data requirements are carefully analysed • A data model is built • a method of describing the data, the structure and how it is interrelated, the constraints that apply • You will be learning the process………

  8. Entity-relationship modelling • An entity is: • An object, person or a thing of interest represented in a database. • Customers, Employees, Products, Orders. • Each entity will normally be represented by a table in the relational database. • Entity names are normally in the singular • Customer not Customers

  9. Entity-relationship modelling • An attribute is: • The information held about an entity. • e.g. Customer attributes: • Surname • Forname • Address • Phone number • Attributes are normally stored in fields.

  10. Car model Car Colour Registration number Date of Registration Cost Attribute Entity Attribute Attribute Attribute Attribute Question - Entity or Attribute?

  11. Car Table

  12. Question • A database is to store details of the following • students at college • A level courses • Write down suitable entity names for these • For each entity write down possible attributes • Draw the database table that would represent these details

  13. Entity relationships • The link between two entities is called a relationship. • Often shown as a Diagram. Entity Relationship Diagrams (ER Diagrams) CAR OWNER

  14. MAN Is married to WOMEN MOTHER CHILD PATIENT STUDENT ORDER TREATMENT CLASS PRODUCT Types of relationships • One to one (1:1) • One to many (1:n or 1:*) • Many to Many (n:n*:*)

  15. OWNER CAR CAR OWNER Question • A car has one registered owner with the DVLA • One person may own several vehicles • Draw an entity-relationship diagram to represent this is owner of is owned by

  16. BLIND PERSON GUIDE DOG GUIDE DOG BLIND PERSON Question • It is possible for a blind person to be given one guide dog • A guide dog will look after just one blind person • Draw the entity-relationship diagram to represent this keeps guides

  17. ITEM ORDER ORDER ITEM Question • An item can be ordered by a customer • A customer may order as many items as is needed • An item may appear on orders made by different customers • Draw an entity-relationship diagram to represent the item and the order appears on consists of

  18. ORDER ORDERITEM ORDER ITEM ITEM Dealing with many-to many relationships • Many-to-many relationships cannot be implemented easily in a database • It is important to break up many to many relationships by creating an extra entity New Entity

  19. Question • A library lends out books to borrowers • Each borrower may borrow several books • There may be more than one copy of a book • Each book title is identified by its ISBN • Each book copy is identified by a unique number • Draw the entity-relationship diagram to represent this system

  20. Answer BOOKTITLE has BORROWER BOOKCOPY

  21. Answer BOOKTITLE has BORROWER BOOKCOPY makes of LOAN

  22. Summary • A relational database can be modelled using an entity-relationship diagram • There are four degrees of relationships • one to one (1:1) • many to one (n:1) • one to many (1:n) • many to many (n:n) • many-to-many relationship should be broken up as they cannot easily be represented in a database

  23. Glossary Database: a structured collection of data Data model: a method of describing the data, it’s structure and the way it is interrelated Entity: an object or thing of interest about which data is recorded Attribute: A piece of data recorded about an entity Relationship: an association or link between two entities

More Related