RELATIONAL DATABASE DESIGN Basic Concepts a database is

RELATIONAL DATABASE DESIGN Basic Concepts • a database is an collection of logically related records or files • a relational database stores its data in 2 -dimensional tables • a table is a two-dimensional structure made up of rows (tuples, records) and columns (attributes, fields) • example: a table of students engaged in sports activities, where a student is allowed to participate in at most one activity Table Characteristics • each row is unique and stores data about one entity • row order is unimportant • each column has a unique attribute name • each column (attribute) description (metadata) is stored in the database • Access metadata is stored and manipulated in the rows of the Design View tables • column order is unimportant • entries in a column have the same data type MIS-DB-Design 1 PA-5 -Appendix

RELATIONAL DATABASE DESIGN Primary Keys • a primary key is an attribute, or a collection of attributes whose value(s) uniquely identify each row in a relation • a primary key must be minimal (that is, must not contain unnecessary attributes • we assume that a student is allowed to participate in at most one activity • the primary key in the above table is Student. ID • what if we allow the students to participate in more than one activity? • in this table, the two attributes, {Student. ID, Student. ID Activity}, Activity constitute the primary key • a multi-attribute primary key is called a concatenated key, key (composite key) and its members are called secondary keys MIS-DB-Design 2 PA-5 -Appendix

RELATIONAL DATABASE DESIGN Foreign Keys • a foreign key is an attribute or a collection of attributes in a relation, whose values match the values of a primary key in some relation • example: the STATE and CITY relations below STATE relation: • primary key in STATE relation: State. Abbrev • primary key in CITY relation: {State. Abbrev, State. Abbrev City. Name} City. Name • foreign key in CITY relation: State. Abbrev MIS-DB-Design 3 PA-5 -Appendix

RELATIONAL DATABASE DESIGN Alternate Database Representations • an alternative representation of the previous database is • STATE = {State. Abbrev, State. Name, Union. Order, State. Bird, State. Population} • CITY = {State. Abbrev, City. Name, City. Population} Functional Dependency • a functional dependency is a relationship among attributes • attribute B is functionally dependent on attribute A if given a value of attribute A we can uniquely look up the corresponding value of attribute B • attribute A is the determinant of attribute B is functionally dependent on attribute A • in the STATE relation above, State. Abbrev is a determinant of all other attributes, since specifying its value would allow us to determine the values of all other attributes uniquely by table lookup • in the STATE relation, the attribute State. Name is also a determinant of all other attributes • in the CITY relation above, the attributes State. Abbrev and City. Name together are a determinant of the attribute City. Population • in the CITY relation, the attribute City. Name is not a determinant of the attribute City. Population because multiple cities in the table may have the same name MIS-DB-Design 4 PA-5 -Appendix

RELATIONAL DATABASE DESIGN Functional Dependency Formally, given two attributes A and B, we say that B is functionally dependent on attribute A if ti(A) = tj(A) ti(B) = tj(B) for i j where ti(A) means A’s value in the ith record. Notice that the reverse is not necessarily true. Example: Customer Name is dependent on Customer ID but the reverse is not true assuming that two customers can have the same name. MIS-DB-Design 5 PA-5 -Appendix

RELATIONAL DATABASE DESIGN Dependency Diagrams • a dependency diagram is a pictorial representation of all functional dependencies in a database • an attribute is represented by a rectangle • an arrow is drawn from the rectangle for attribute A to the rectangle for attribute B whenever attribute A is the determinant of attribute B • example: students sports activity - I consists of the relation ACTIVITY = {Student. ID, Activity, Fee} Student. ID Activity Fee • example: students sports activity - II consists of the relation ACTIVITY = {Student. ID, Activity, Fee} Student. ID MIS-DB-Design Activity 6 Fee PA-5 -Appendix

RELATIONAL DATABASE DESIGN Partial Dependencies • a partial dependency is a functional dependency in which the determinant is a part of the primary key • example: ACTIVITY = {Student. ID, Activity, Fee} • the dependency between the attributes Activity and Fee is a partial dependency Student. ID Activity Fee Transitive Dependencies • a transitive dependency dependen is a functional dependency in which none of the attributes involves attributes of a primary key ( none of them is a part of the primary key) • example: ACTIVITY = {Student. ID, Activity, Fee} • the dependency between the attributes Activity and Fee is a transitive dependency Student. ID MIS-DB-Design Activity 7 Fee PA-5 -Appendix

RELATIONAL DATABASE DESIGN Database Anomalies • anomalies are problems caused by bad database design • problems mean here undesirable irregularities of tables • example: ACTIVITY = {Student. ID, Activity, Fee} • an insertion anomaly occurs when a row cannot be added to a relation, because not all data is available • example: we want to store the fact that diving costs $175, but cannot enter this fact into the table until a student takes up scuba-diving • a deletion anomaly occurs when data is deleted from a relation, and unintentionally other critical data are lost • example: by deleting a record (say, Student. ID = 100), the fact that skiing costs $200 is lost • an update anomaly occurs when one attribute is changed, but the DBMS must make more than one change to reflect that single change • example: if the cost of swimming changes, then all entries with swimming Activity must be changed too MIS-DB-Design 8 PA-5 -Appendix

RELATIONAL DATABASE DESIGN Cause of Anomalies • anomalies are mostly caused by the following: • data redundancy (replication of the same field in multiple tables) ( repeating sections) • partial dependency • transitive dependency • example: ACTIVITY = {Student. ID, Activity, Fee} Student. ID Activity Fee • in this example, there is a partial dependency, which the cause of all the anomalies MIS-DB-Design 9 PA-5 -Appendix

RELATIONAL DATABASE DESIGN Cause of Anomalies (Cont. ) • a two-table solution: • STUDENTS = {Student. ID, Activity} • ACTIVITIES = {Activity, Fee} Student. ID Fee Activity • the above relations do not have any of the anomalies • we can add the cost of diving in ACTIVITIES even though no one has taken it in STUDENTS • if Student. ID 100 drops Skiing, no skiing-related data will be lost • if the cost of swimming changes, that cost need only be changed in one place only (the ACTIVITIES table) • the Activity field is replicated in the two tables, but without this replication we cannot join the two tables MIS-DB-Design 10 PA-5 -Appendix

RELATIONAL DATABASE DESIGN Good Database Design Principles 1. no redundancy • a field is stored in only one table, unless it happens to be a foreign key • replication of foreign keys is permissible, because they allow two tables to be joined together 2. no partial dependencies • the dependency diagram of any relation in the database should contain no partial dependencies 3. no transitive dependencies • the dependency diagram of any relation in the database should contain no transitive dependencies • normalization is the process of eliminating partial and transitive dependencies • as we normalize the relations, larger tables are split into smaller tables with one common foreign key field • there are five normal forms (NF), as given below First Normal Form (1 NF) Second Normal Form (2 NF) Third Normal Form (3 NF) Boyce Codd Normal Form (BCNF) Fourth Normal Form (4 NF) Fifth Normal Form (5 NF) MIS-DB-Design 11 PA-5 -Appendix

RELATIONAL DATABASE DESIGN First Normal Form • a relation is said to be in the first normal form (1 NF) if it does not contain any nested relation. In other words, all attributes are atomic. • IMPORTANT NOTE: many authors call the nested relations repeating section. This name is missleading. The nested relations involve many different instances of the same attribute for one record. There is nothing repeating in this anomaly. • example: CLIENT table has nested relations. CLIENT = {Client. D, Client. Name, Vet. ID, Vet. Name, {Pet. ID, Pet. Name, Pet. Type} } MIS-DB-Design 12 PA-5 -Appendix

RELATIONAL DATABASE DESIGN • In order to eliminate the nested relation, pull out the nested relation and form a new table • Be sure to include the old key in the new table so that you can connect the tables back together. • When a table contains no nested relations, we say that it is in first normal form. Client Name Client ID Vet Name Vet. ID transitive Client ID Pet Name Pet Type Second Normal Form • In order to eliminate the partial dependency, split the table again. • a table is said to be in the second normal form (2 NF) if it does not contain any partial dependencies, that is, each nonkey column in a table depends on the entire key. • example: partial dependencies in the relation • now there are no partial dependencies, hence we need not do anything; • the relation still has some anomalies MIS-DB-Design 13 PA-5 -Appendix

RELATIONAL DATABASE DESIGN Third Normal Form • In order to eliminate transitive dependency, we split the table again. • a table of 2 NF is said to be in the third normal form (3 NF) if it does not contain any transitive dependencies, that is, Each nonkey column depends on the whole key and nothing but the key. • in the 3 NF, each determinant is a primary key • example: conversion of CLIENT relation to the 3 NF: • CLIENTS = {Client. ID, Client. Name, Vet. ID} • PETS = {Client. ID, Pet. Name, Pet. Type} • VETS = {Vet. ID, Vet. Name} Client Name Client ID Vet. ID Client ID Pet Name Vet Name Pet Type • note that the tables can be joined to yield a table in the first normal form • the Client. ID and Vet. ID fields are replicated, but they are both foreign keys MIS-DB-Design 14 PA-5 -Appendix

RELATIONAL DATABASE DESIGN Third Normal Form (Cont. ) • example: CLIENT database in the third normal form with table relationships • the database consists of three types of entities, stored as distinct relations in separate tables: • clients (CLIENTS) • pets ( PETS) • vets (VETS) • there is no redundancy (only foreign keys are replicated) • there are no partial and transitive dependencies MIS-DB-Design 15 PA-5 -Appendix

RELATIONAL DATABASE DESIGN Example: Hardware Store Database • the ORDERS table : MIS-DB-Design 16 PA-5 -Appendix

RELATIONAL DATABASE DESIGN Example: Hardware Store Database (Cont. ) • ORDERS = {Order. Numb, Prod. Descr, Cust. Code, Order. Date, Cust. Name, Prod. Price, Quantity} • dependency diagram of the ORDERS table: partial Order Numb Prod Descr Cust Code Cust Name Order Date transitive Prod Price Quantity partial • conversion of the hardware store database to 2 NF • QUANTITY = {Order. Numb, Prod. Descr, Quantity} • PRODUCTS = {Prod. Descr, Prod. Price} • ORDERS = {Order. Numb, Cust. Code, Order. Date, Cust. Name} • dependency diagram of the hardware store database in 2 NF Order Numb Prod Descr Quantity Prod Price transitive Order Numb MIS-DB-Design Cust Code 17 Order Date Cust Name PA-5 -Appendix

RELATIONAL DATABASE DESIGN Example: Hardware Store Database (Cont. ) • conversion of the ORDERS relation to 3 NF • QUANTITY = {Order. Numb, Prod. Descr, Order. Quant} • PRICE = {Prod. Descr, Prod. Price} • ORDERS = {Order. Numb, Cust. Code, Order. Date} • CUSTOMERS = {Cust. Code, Cust. Name} • dependency diagram of the hardware store database in 3 NF Order Numb Prod Descr Order Quant Order Date Cust Code Prod Price Cust Name • table relationships for the hardware store database MIS-DB-Design 18 PA-5 -Appendix

RELATIONAL DATABASE DESIGN Example: Video Store Database • the CUSTOMER relation: • the RENTAL-FORM relation: • a customer can rent multiple videos as part of the same transaction • however, the Video. ID fields will be different for each video • multiple copies of the same video are allowed • the copy# field stores the number of the copy • video rental depends on the title and not on the day • the database still contains some anomalies MIS-DB-Design 19 PA-5 -Appendix

RELATIONAL DATABASE DESIGN Example: Video Store Database (Cont. ) • relations for the video store database • CUSTOMER = {Customer. ID, Phone, Name, Address, City, State, Zip. Code} • RENTAL-FORM = {Trans. ID, Rent. Date, Customer. ID, Video. ID, Copy#, Title, Rent} • dependency diagram for the video store database Customer ID Trans ID Phone Rent Date Name Customer ID Address Video ID City State Copy# Title Zip. Code Rent • video store database after eliminating partial dependencies • CUSTOMER = {Customer. ID, Phone, Name, Address, City, State, Zip. Code} • RENTALS = {Trans. ID, Rent. Date, Customer. ID} • VIDEOS = {Video. ID, Title, Rent} • Videos. Rented = {Trans. ID, Video. ID, Copy#} MIS-DB-Design 20 PA-5 -Appendix

RELATIONAL DATABASE DESIGN Example: Video Store Database (Cont. ) • dependency diagram for the video store database in 3 NF Customer ID Phone Trans ID Rent Date Name Address Customer ID Trans ID City Video ID State Title Zip. Code Rent Copy# • table relationships for the video store database MIS-DB-Design 21 PA-5 -Appendix

RELATIONAL DATABASE DESIGN Summary of Guidelines for Database Design • identify the entities involved in the database • identify the fields relevant for each entity and define the corresponding relations • determine the primary key of each relation • avoid data redundancy, but have some common fields so that tables can be joined together • ensure that all the required database processing can be done using the defined relations • normalize the relations by splitting them into smaller ones MIS-DB-Design 22 PA-5 -Appendix