Introduction to Relational Databases

1. Introduction to Computer Systems Department of Computer Science and Information Systems Lecturer: Steve Maybank sjmaybank@dcs.bbk.ac.uk Spring 2019 Week 10b: Relational Databases Birkbeck College, U. London

2. Data Stored by an Organisation • Customer records • Payroll records • Sales records • Human resources • The payroll dept should have no access to customer records, the sales dept should have no access to the payroll, but does require customer records, etc. Brookshear, Section 9.2

3. 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

4. File Oriented Information System Payroll records Customer records Employee records Customer service dept Payroll dept Employee dept Brookshear, Section 9.1

5. Database Oriented Information System Customer service dept Integrated database Employee dept All data is stored centrally No duplication Access to the data is controlled Payroll dept Brookshear, Section 9.1

6. The Database is the Product • Google • eBay • Facebook • Amazon … • Client request -> interrogate database -> show results in a web page Brookshear, Section 9.1

7. Very Large Databases 1PB=1015 Bytes = 0.15MB for every person on Earth Estimates are hard to find on the web Birkbeck College

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

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

10. User Interface Birkbeck College

11. Advantages of Splitting AS from DBMS • The user sees only the database model. The true complexity of the database is hidden. • The DBMS and the database can be modified 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

12. 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

13. 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

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

15. 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

16. Table Structure • Each table should correspond to a single concept or task • Each row of a table should be uniquely identified by a key • Avoid including the same information in different tables Brookshear, Section 9.2

17. 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

18. 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

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

20. 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

21. Original Relation Original table cf Brookshear, Section 9.2

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

23. Original Relation Brookshear, Section 9.2

24. Example of a Lossy Decomposition Brookshear, Section 9.2

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

26. 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

27. Problem 4 • Design a relational database containing information about novels, authors and the numbers of pages in the novels. Note that a novel may have more than one author, and the number of pages and the authors may vary from one edition to another. BB Ch. 9, review problem 29

28. Problem 5 • Design a relational database containing information about parts, suppliers, and customers. Each part might be supplied by several suppliers and ordered by many customers. Each supplier may supply many parts and have many customers. Each customer might order many parts from many suppliers; in fact, the same part might be ordered from more than one supplier. BB Ch. 9 review problem 21