Designing Relational Databases

1. Designing Relational Databases Stephen Scott CSE 156

2. Introduction • Now that we know basic database terminology, it’s time to delve into designing one • The problem: given a text-based description (specification) of a design problem, come up with a concrete specification of the database and the SQL statements to implement it • Where to begin???? CSE 156

3. The General Process • Identify the entities (tables), and the attributes (fields) that are associated with each one • Kind of like naming the objects in a software design problem • E.g. a winery is an object, and each has a name • Identify the relationships between the entities, and their cardinality • E.g. a wine comes from a single winery, but one winery makes multiple wines • Use this information to build an initial entity-relationship (ER) model • A graphical representation of our database • Represents each entity, their attributes, and the relationships (including cardinality) between the entities CSE 156

4. The General Process (cont’d) • Identify or add a primary key for each table • Must be unique • Many possibilities • Keep it simple, if possible! • Label the weak entities of the ER model • Entities that depend on other entities • Convert the ER model to SQL • Start with regular entities, then the weak ones, then do the relationships: one-to-one, then one-to-many, then many-to-many CSE 156

5. An Example Specification • The owner of a local scuba store wants to track his customers and their purchases. Each customer has a first and a last name and has a highest certification level (e.g. Open Water, Advanced OW) from some agency (e.g. PADI, NAUI, SSI). Also, each customer has purchased a (possibly empty) set of items from {BCD, regulator, wet suit, dry suit, mask, tank, fins, snorkel}, but at most one item of each type. Each such item has a manufacturer, and multiple manufacturers make similar items. Each manufacturer has a name and address. For simplicity, assume that each manufacturer makes only one item of each type. • (Also assume that store owner is insane and destroys all serial numbers and other identifying information on each product.) CSE 156

6. Step 1 • What are the entities and their attributes? CSE 156

7. Step 1 • What are the entities and their attributes? • Customer • First name (string) • Last name (string) • Certification • Level (one of {Open Water, Advanced OW, …}) • Agency (one of {PADI, NAUI, SSI, …}) • Item • Type (one of {BCD, regulator, …}) • Manufacturer • Name (string) • Address (string) CSE 156

8. Step 2 • What are the relationships between the entities? CSE 156

9. Step 2 • What are the relationships between the entities? • A customer has a certification level • Each customer has one highest level, and each level can be held by multiple customers • One-to-many • A customer purchases items • Each customer can purchase multiple items, and each item can be purchased by multiple customers • E.g. each customer can own 0 or more from {BCD, regulator,…}, and a regulator can be owned by multiple customers • Many-to-many CSE 156

10. Step 2 (cont’d) • What are the relationships between the entities? • An item is made by a manufacturer • Each item has only one manufacturer, but a manufacturer can make multiple items • One-to-many CSE 156

11. Step 3: Initial ER Model • Entities represented by rectangles • Attributes represented by ellipses, connected to entities by lines • Relationships represented by diamonds • Lines connect entities to relationships, cardinality labeled by letters CSE 156

12. first name last name buys has customer item manufacturer certification level address agency makes name Step 3: Initial ER Model itemtype M N N N 1 1 CSE 156

13. Step 4: Primary Keys • Combination of level and agency uniquely defines a certification • Customer may not be uniquely identified by first and/or last name, so we’ll add a customer ID (int) • Even if combination is unique for each customer, a numeric ID is less prone to spelling errors when inserting, querying, etc. • We’ll do the same with manufacturer • With respect to a particular manufacturer, type uniquely identifies item • More on this later CSE 156

14. itemtype manufacID custID first name last name buys has customer item certification manufacturer level address agency makes name Step 4: Primary Keys M N N N 1 1 CSE 156

15. Step 5: Label Weak Entities • Are there any entities that cannot exist without another? CSE 156

16. Step 5: Label Weak Entities • Are there any entities that cannot exist without another? • A manufacturer and a customer and a certification can each exist on its own • An item cannot exist unless it is manufactured (but it doesn’t depend on a customer) • So item fully participates in the makes relation, and is connected by a double line • Also, type uniquely defines an item, but only when a manufacturer is specified (since multiple manufacturers make e.g. BCDs, but each manufacturer only makes one) • So item is a weak entity and gets a double box CSE 156

17. itemtype manufacID firstname custID lastname buys has customer item certification manufacturer agency level address makes name Step 5: Label Weak Entities M N N N 1 1 CSE 156

18. Step 6: Convert to SQL • Regular (non-weak) entities: CREATE TABLE customer ( customerID int NOT NULL AUTO_INCREMENT, firstname varchar(30) NOT NULL, lastname varchar(30) NOT NULL, PRIMARY KEY (customerID) ) type=MyISAM; CREATE TABLE manufacturer ( manufacID int NOT NULL AUTO_INCREMENT, name varchar(50) NOT NULL, address varchar(100) NOT NULL, PRIMARY KEY (manufacID) ) type=MyISAM; CREATE TABLE certification ( level enum(‘Open Water’,’Advanced OW’,’Rescue Diver’,. . .) NOT NULL, agency enum(‘PADI’, ‘NAUI’, ‘SSI’, . . .) NOT NULL, PRIMARY KEY (level,agency) ) type=MyISAM; CSE 156

19. Step 6: Convert to SQL (cont’d) • Weak entity: • item is uniquely identified if manufacturer is also specified, so add manufacID as a primary key for item CREATE TABLE item ( itemtype enum(‘BCD’,’regulator’,’wet suit’,. . .) NOT NULL, manufacID int NOT NULL, PRIMARY KEY (manufacID,itemtype) ) type=MyISAM; CSE 156

20. Step 6: Convert to SQL (cont’d) • No one-to-one relationships, so do one-to-many: • The idea is as follows: if a certification can be held by multiple customers, then we need to relate each customer to the held certification • Easiest way is to add certification’s primary key(s) to item, to serve as foreign keys: CREATE TABLE customer ( customerID int NOT NULL AUTO_INCREMENT, firstname varchar(30) NOT NULL, lastname varchar(30) NOT NULL, level enum(‘Open Water’,’Advanced OW’,’Rescue Diver’,. . .) NOT NULL, agency enum(‘PADI’, ‘NAUI’, ‘SSI’, . . .) NOT NULL, PRIMARY KEY (customerID) ) type=MyISAM; • We have a similar case for the makes relation, but in this case we don’t need to update the item table, since we already added manufacID to it (since item was weak) • Note that if item were not weak, then we would add manufacID as a foreign (but not primary) key, to capture the relationship CSE 156

21. Step 6: Convert to SQL (cont’d) • Many-to-many relationships: • Each item can be purchased by multiple customers, and each customer can purchase multiple items • Thus we need to be able to relate each customer to multiple purchases, and vice-versa • Create a new table that takes the primary keys of the related entities as its primary keys • Note that since item is weak, one of its primary keys is manufacID, so we need to add that as well CSE 156

22. Step 6: Convert to SQL (cont’d) • Many-to-many relationships (cont’d): CREATE TABLE purchase ( itemtype enum(‘BCD’,’regulator’,’wet suit’,. . .) NOT NULL, manufacID int NOT NULL, customerID int NOT NULL, PRIMARY KEY (manufacID,itemtype,customerID) ) type=MyISAM; (Where’d the AUTO_INCREMENT go?) CSE 156

23. Final Notes • Relationships can have attributes as well! • E.g. if a purchase has a date CREATE TABLE purchase ( itemtype enum(‘BCD’,’regulator’,’wet suit’,. . .) NOT NULL, manufacID int NOT NULL, customerID int NOT NULL, dateofpurchase timestamp(8), PRIMARY KEY (manufacID,itemtype,customerID) ) type=MyISAM; CSE 156

24. buys customer item Final Notes (cont’d) N In ER diagram: (could also add date and certification number to “has” relationship) • Also, be careful about one-to-one relationships! • Ask if they’re really necessary M dateofpurchase CSE 156