1 / 22

Introduction to Computer Systems

Introduction to Computer Systems. Lecturer: Steve Maybank Department of Computer Science and Information Systems sjmaybank@dcs.bbk.ac.uk Autumn 2013 Week 11a: Relational Databases. Definition of a Database.

frisco
Download Presentation

Introduction to Computer 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. Introduction to Computer Systems Lecturer: Steve Maybank Department of Computer Science and Information Systems sjmaybank@dcs.bbk.ac.uk Autumn 2013 Week 11a: Relational Databases Birkbeck College, U. London

  2. Definition of a Database • A collection of data with internal links that make the information accessible from a variety of perspectives. Brookshear, Section 9.1

  3. Different Perspectives In an employee database, list • All employees in personnel • All job titles in Accounts • All employees under 30 • All departments with 50 or more employees • … Brookshear, Section 9.2

  4. Very Large Databases 1PB=1015 Bytes = 0.15MB for every person on Earth See en.wikipedia.org/wiki/Petabyte Other candidates: YouTube, FaceBook, Amazon, CIA, Library of Congress Birkbeck College

  5. Database Model • User’s concept of the database. • Eg. in a relational database the user sees all the data in tables. • The actual storage and management of the data may not correspond to the database model. Brookshear, Section 9.1

  6. Layers in a Database Implementation • Applications Software: user interface, including implementation of the database model. • Database Management System: updates the database and supplies data to the applications software. Actual data Applications Software DBMS User Brookshear, Section 9.1

  7. Advantages of Splitting AS from DBMS • The user sees only the database model. The true complexity of the database is hidden. • The DBMS can be changed without affecting users. • The AS can be tailored for different groups of users. • Easier access control because all access is through a single DBMS. Brookshire, Section 9.1

  8. Database Consisting of a Single File If a customer has no orders, then all information about that customer is lost. cf. Brookshear, Section 9.1

  9. Relational Model • All data is organised into rectangular tables called relations. • Each row (or tuple) is a single data item. • Each column is an attribute, taking values in a specified domain. • The table heading is not a row in the table. Table heading Table Brookshear, Section 9.2

  10. Advantages and Disadvantages Advantages: • When a table is updated, the information in the other tables is retained. • Information is not duplicated. Disadvantage: • Once the relations are established they cannot be easily changed. Brookshear, Section 9.2

  11. Relational Database Order file Customer file What is the address of Sperry? What is the total value of the orders placed with Honeywell? How many different products are there on order? How many different products are there? Birkbeck College

  12. Table Structure • Each table should correspond to a single concept or task. • Each row of a table should be uniquely identified by a key. • The table design should avoid multiple copies of information. Brookshear, Section 9.2

  13. Keys • Primary key: an attribute whose value uniquely identifies a tuple. • Composite key: a minimal set of attributes whose values together uniquely identify a tuple • Foreign key: set of attributes pointing to a primary key or a composite key in another table. cf. Brookshear, Section 9.2

  14. Examples of Keys Order file Customer file Primary keys: Order Num in the Order file, Customer Num in the Customer file Foreign key: Customer Num in the Order file Birkbeck College

  15. Problem • Find the names of all customers who have ordered products with a total value of £3000 or less. Birkbeck College

  16. Lossless Decomposition • In lossless decomposition a relation (table) is decomposed into smaller relations without loss of information. • Aim: to produce a better table structure. Brookshear, Section 9.2

  17. Original Relation Original table cf Brookshear, Section 9.2

  18. Example of a Lossless Decomposition Order file Customer file Brookshear, Section 9.2

  19. Original Relation Brookshear, Section 9.2

  20. Example of a Lossy Decomposition Brookshear, Section 9.2

  21. Problem 2 What is the difference in the information supplied by the single relation and the two relations Brookshear, Ch. 9, Problem 28

  22. Problem 3 • Let R(X,Y,Z) be a relation with attributes X, Y, Z, and let R(X,Y) and R(Y,Z) be relations obtained by decomposing R(X,Y,Z). • Prove that if Y is a key for R(X,Y), then the decomposition is lossless. See S. Sumathi and S. Esakkirajan, Fundamentals of relational database management systems. Birkbeck College

More Related