This assignment, Assignment #3, is due Tuesday April 30th, and consists of designing a database using Entity-Relation and/or Normalization techniques.
What you are to do: You are to design an SQL database. That is, you are to write out the SQL create statements for your database. You should not then populate your database with any data. I would strongly urge you to execute your create statements within sqlplus to make sure that they're legal statements. You should send me the SQL create statements as plain text in an email, not as an attachment, in case I want to paste the statements into sqlplus myself to check them. But the remainder of your design, especially any ER diagrams you might use, should be handed in at the beginning of class as hardcopy. Your handin must include a clear discussion, in English, of how you went about doing your design. Just the SQL and a diagram or two with no text is not acceptable. You must also include an explanation.
You should add one additional entity of your own choosing to your database, as well as adding at least one additional attribute to each of your entities and relations, beyond the ones I have listed. You also may choose to replace a few of the atributes I have listed below by ones you feel are better. One of your goals should be to have tables that need to be linked by meaningful foreign keys. If you database has no such links, add to the initial design until it does.
There are three alternative database descriptions below, each of similar complexity. You are to work only on the one that matches the number I gave you for doing Assignment #2. The exact number of tables you'll need may not be immediately apparent from the description below.
Database A, for 3315 student numbers 1, 4, 7, 10, 13, 16: This models a fish wholesaler and consists of employees with name, address, phone, pay rate, SSN. Also, stocks of fish with fish name, weight, date bought, employee(s) expert on this fish, quality rating. Also, pickup and delivery trucks that have employee drivers and fish stocks loaded with supplier name or customer name attached.
Database B, for 3315 student numbers 2, 5, 8, 11, 14, 17: This models a nursery school with teachers with name, address, phone, cell phone, pay rate. Also, parents with name, address, home phone, cell phone, business phone, children. Children, with name, age, parents, allergies, food preferences, comment, (a child may have two parents with separate addresses and information).
Database C, for 3315 student numbers 3, 6, 9, 12, 15, 18: This models a cattle ranch with cattle with birth date, weight (if available), tag (like a SSN), sex (cow, bull, steer), current feed, purchase price. Also, equipment such as trucks and tractors, each with name, year, mileage (if applicable), license number (if applicable). Also, employees with name, address, phone, cell phone, pay rate, equipment they use.
Go to COM3315 home page (Gateway)
Return to Prof. Futrelle's home page