Introduction to Computer Systems Department of Computer Science

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

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 2

Definition of a Database n A collection of data with internal links that make the information accessible from a variety of perspectives Brookshear, Section 9. 1 3

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

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

The Database is the Product n n n Google e. Bay Facebook Amazon … Client request -> interrogate database -> show results in a web page Brookshear, Section 9. 1 6

Very Large Databases Google (2020) estimate 10, 000 PB CERN Data Centre (2017) 200 PB German Climate Computing Centre (DKRZ) (2015) 500 PB 1 PB=1015 Bytes = 0. 15 MB for every person on Earth Estimates are hard to find on the web Birkbeck College 7

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

Database Model n n n 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 9

User Interface Birkbeck College 10

Advantages of Splitting AS from DBMS n n 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 11

Database Consisting of a Single File Order num Customer name Customer address Price Due date Product 37 Sperry 1 The Lane £ 1000 1. 7. 16 Plate 43 Univac 15 Retail Road £ 2000 5. 5. 16 Case 20 Honeywell 205 North Street £ 3400 2. 4. 16 Panel If a customer has no orders, then all information about that customer is lost cf. Brookshear, Section 9. 1 12

Relational Model Table heading Table n n Customer Num Name Address 102 Sperry 1 The Lane 103 Univac 15 Retail Road 54 Honeywell 205 North Street 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. Brookshear, Section 9. 2 13

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 14

Relational Database Order Num Customer Num Price Due date Product 37 102 £ 1000 1. 7. 16 Plate 43 103 £ 2000 5. 5. 16 Case 20 54 £ 3400 2. 4. 16 Panel Customer Name Num Address 102 Sperry 1 The Lane 103 Univac 15 Retail Road 54 Honeywell 205 North Street 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 15

Table Structure n n n 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 16

Keys n n n 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 17

Examples of Keys Order Num Customer Num Price Due date Product 37 102 £ 1000 1. 7. 16 Plate 43 103 £ 2000 5. 5. 16 Case 20 54 £ 3400 2. 4. 16 Panel Customer Name Num Address 102 Sperry 1 The Lane 103 Univac 15 Retail Road 54 Honeywell 205 North Street 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 18

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

Lossless Decomposition n n 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 20

Original Relation Order Customer Price Due Product Name Address no. date 37 102 £ 1000 1. 7. 16 Plate Sperry 1 The Lane 43 103 £ 2000 5. 5. 16 Case Univac 15 Retail Road 20 54 £ 3400 2. 4. 16 Panel Honeywell 205 North Street Original table cf Brookshear, Section 9. 2 21

Example of a Lossless Decomposition Order Custo Price Due Product no. mer date no. 37 43 20 102 103 54 £ 1000 £ 2000 £ 3400 1. 7. 16 5. 5. 16 2. 4. 16 Plate Case Panel Customer no. Name 102 Sperry 1 The Lane 103 Univac 15 Retail Road 54 Honeywell 205 North Street Order file Address Customer file Brookshear, Section 9. 2 22

Original Relation Empl Id Job Title Dept 203 Assistant Finance 17 Manager Finance 18 Manager Planning Brookshear, Section 9. 2 23

Example of a Lossy Decomposition Empl Job Title Id Job Title Dept 203 Assistant Finance 17 Manager Finance 18 Manager Planning Brookshear, Section 9. 2 24

Problem 2 Name What is the difference in the information supplied by the single relation Department Tel. No. Jones Sales 555 -2222 Smith Sales 555 -3333 Baker Personnel 555 -4444 and the two relations Name Department Tel. No. Jones Sales 555 -2222 Smith Sales 555 -3333 Baker Personnel 555 -4444 Brookshear, Ch. 9, Problem 28 25

Problem 3 n n 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 26

Problem 4 n 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 27

Problem 5 n 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 28