Relational Database Design I

1. Relational Database Design I Database Principles

2. Database Principles Good Tables versus Bad Tables: • A table in a relational database is good if it is about one thing. A table that is not good is bad. • Problem of RDB Design: Build good tables and convert bad tables into good tables. • What is a table “about”? The key to the answer is the key. • The key to a table is the identifier of whatever the table is about.

3. Database Principles Good Table Examples: Supplier is good because its key is Sno, which identifies different suppliers, and each column in Supplier – Sname and Location – is a pieceof information about Suppliers. Exercise: Explain why Part is a good table. Supplies is good because its key is (Sno,Pno), which identify individual orders, and the only other column in the table – O_date – is a piece of info about individual orders.

4. Database Principles Bad Table Example: Even though this table has info about Suppliers, Parts and Supplies its key is (Sno,Pno). And so this table is “about” whatever its key identifies, namely Supplies. But the table contains various columns – Sname, Location, Pdesc, Color – that are not about Supplies but about Supplier and Part respectively. So this table is “bad” and the process of RDB Design would be to reform this table intothe three tables on the previous slide. Quick Observation: If your tables come from an ERD they are normally pretty “good”.

5. Database Principles Bad Tables can be Useful, if not Good: • The previous table, called bad, is so only if it is a permanent table. As part of an on-going query it is not considered bad since data is not stored permanently in this format. • Suppose you never, ever expect to look at the supplies table information without knowing the name of the supplier and the part supplied. If the join table does not exist then you will always have to construct the join. This can be time consuming and to save that time you might keep the table in pre-joined, permanent form.

6. What’s so Bad about a Bad Table? • Suppose instead of three tables we only have one table Database Principles

7. Insert Anomaly: • We want to add supplier A-1 to the database but for now we have no parts that A-1 supplies. Since the key to the table is (Sno,Pno) we can’t add a row until we have values for both Sno and Pno. s5 A-1 Phil null null null null not permitted Database Principles

8. Update Anomaly: • What happens if the part, p2, changes its color from yellow to purple? • We must search every row of the join-table and change every instance of yellow to purple in rows involving the supplying of part, p2. multiplechanges Database Principles

9. Update Anomaly (cont): • This one change in the real world makes for many changes in the database. • What if we mess up and end up not making all changes? • Now, what color is p2? twodifferentcolors purple Database Principles

10. Delete Anomaly: • What if we cancel the order for bolts from supplier, s2? • A consequence is that we lose all information about the supplier, s2. Database Principles

11. So What Can Be Done? • Suppose we keep these two tables and we also keep this table Any ideas? Database Principles

12. Database Principles So What Can Be Done? (cont) • There is the issue of data consistency. • Given the same information stored in several places it becomes a big job to make sure this data is consistent. • If we lose data consistency then all the data essentially becomes “noise”.

13. Some Notation: • A table is sometimes called a relation. • We use R, S and T and nearby letters to represent tables. • Table columns are also called attributes. • We use A, B and C and nearby letters to represent columns. • The possible values in a column A of table R are called the domain of A, dom(A). • Table schemas are lists of table columns. • We use R, S and T to represent schemas. Database Principles

14. Some Notation (cont): • Table rows are also called tuples. • We use r, s and t and nearby letters to represent rows Subsets of a table schema are represented by X, Y and Z and nearby letters. • X R is a subset of the list of all columns in a table. • r[A] is the value in row r column A. • r[X] is the subrow of r consisting of the values in the columns of X. ∩ Database Principles

15. Database Principles Table Name: Letter from middle of alphabet – R, S, T Column Name: Letter from beginning of alphabet – A, B, C R = { A, B, C, D, E, F }; the schema of R r = ( a2, b2, c2, d2, e2, f2 ) r ∩ X = { A, B, C } R: X is a subset of the schema; a letter at the end of the alphabet r[A] = (a2); a singleton tuple r[X] = ( a2, b2, c2 ); a subset of r Example:

16. What is a Key to a Table? Def’n: For any table R, if X is a subset of R, then X is a key to the table R if the following is true: for any two rows r and s of R, if r[X] = s[X] then r = s. In other words, r and s are the same row. In other words, any two rows that agree on X agree everywhere • A key is a set of columns of a table whose values uniquely identify distinct rows of the table. • A key is a set of columns of a table such that if you know the values of the columns in the key, there is at most one row in the table with these values. One key to Supplier is {Sno}; are there any others? What about {Sno, Location}? Database Principles

17. Not all Columns are Keys: • Why isn’t {Location} a key to Supplier? • Because at some point in the future we may add a new supplier who comes from Boston, for example. Database Principles

18. Keys, Keys and More Keys: • We saw earlier that {Sno, Location} is also a key to Supplier. It is called a superkey. Def’n: For a table R, if X is a key of R and X  Y, then Y is a superkey of R. Any set of columns that contains a key to a table is a superkey of the same table. Superkeys are keys too. Supplier has many superkeys – {Sno}, {Sno,Sname}, {Sno,Location}, Supplier Database Principles

19. Database Principles Exercise: • Prove that any table has at least one superkey. • Answer: The schema itself is a superkey.

20. Keys, Keys and More Keys: • Some keys are smaller (fewer columns) than others. • Some keys can’t be made any smaller (fewer columns). Def’n: For a table R, if X is a key of R and for any Y  X, we know that Y is not a key of R, then X is called a candidate key of R. The only candidate key to Supplier is {Sno}. Database Principles

21. Database Principles Candidate Keys: • What made us decide {Sno} was a candidate key of Supplier? • What makes us say the {Location} is not a key to Supplier? • What makes us decide that something is a key is a rule about the real world that makes it so. We call such a rule and Enterprise Rule. We know that no two suppliers were assigned the same number We know there is no rule saying suppliers must come from different locations.

22. Multiple Candidate keys (1): • In the table below there are 2 possible candidate keys: {StudentID} and {SSN} Database Principles

23. Multiple Candidate keys (2): • In the table below there are 2 possible candidate keys: • Candidate keys don’t need to be the same size. {CourseID,SectionID} and {RoomNum, BldgID,TimeSlot} Database Principles

24. Database Principles Primary key: • What do you do when you have candidates? • The candidate key that wins the election is called the primary key. There is only one primary key in a table. hold an election

25. Primary Key Examples: • What are the primary keys of each of the tables below. pk = {Sno} pk = {Pno} pk = {Sno,Pno,O_date} or {Sno,Pno} the difference in what is the primary key is determined by the Enterprise Rules Database Principles

26. Database Principles Foreign Keys: • What makes someone a foreigner? • What makes a set of columns a foreign key? • In the Supplies table both {Sno} and {Pno} are foreign keys because they are primary keys in other tables; Supplier and Part respectively. being physically in a country other than their own columns are a foreign key if they are a primary key in some other table

27. Database Principles What are the Foreign Keys? fk fk fk fk fk