assignment 3: database design Solution

goals




This assignment aims to help you learn to:




informally design a database to represent a real-world scenario



express your design as an SQL schema



reason about functional dependencies and use normal forms



design context




You are helping design a database back-end for Wet World's dive booking app. Qualied divers will use the app to book dives with a dive monitor and dive site.




Divers must be at least 16 when a dive occurs and have an open-water certication from one of: NAUI, CMAS, or PADI. One diver, the \lead," books a dive, possibly to include other qualied divers, up to the limit the monitor is willing to take, and taking into account the number the dive site has capacity for at that date and time.




Each monitor has their own price list, according to whether a dive will be morning (9:30 to 11 a.m.), afternoon (12:30 to 2 p.m.) or night (8:30 to 10 p.m.), and whether the dive will be open water, cave diving, or deeper than 30 meters. The price charged by a monitor includes any fees a dive site charges per diver, and a monitor may have booking privileges with one or more dive sites. Monitors also have a maximum group size for each category: open water, cave, or deeper than 30 meters, and they set the maximum to 0 if they do not supervise that category of dive.




All dive sites provide 12 litre aluminum tanks, weight belts and bouyancy compensation vests as part of their fee. Some also provide mask, regulator, ns, and wrist-mounted dive computer, each for additional fees. In addition some sites provide free services from among: video of dives, snacks, hot showers, towel service. Each site lists a maximum number of divers, combining one or more bookings, allowed on site at any daylight hour, and lists smaller maxima for night, cave, or deeper than 30 meter dives. Each site lists one or more dive types they provide from: open water, cave dive, or beyond 30 meters.




A lead diver may book as many dives as they can nd monitors and dive sites for, but only one dive at a given date and time. No monitor is allowed to book more than two dives per 24 hour period, in order to keep their blood nitrogen at safe levels. The lead diver provides credit card information, email addresses and dive certication for all divers, and credit card information to secure the dive(s).




All divers may, optionally, rate a site on a scale of 0 (very poor) to 5 (excellent), and lead divers may rate monitors on the same scale.







1
schema




Dene a schema in DDL, stored in le schema.ddl. Your goals, in priority order, are:




Allow as few redundancies as possible.



Allow as few NULL or DEFAULT values as possible.



Without using assertions or triggers, enforce as many constraints from the database description as you can.



Wherever goals are in con

ict, implement the higher-priority goal. Also you should:




Dene a primary key for each table, and in addition use UNIQUE and dene foreign keys where appropiate.



You will be repeatedly re-importing schema.ddl as you develop and test it, so it should begin with the following:




drop schema if exists wetworldschema cascade; create schema wetworldschema;




set search_path to wetworldschema;




At the top of schema.ddl use a comment to document which constraints could not be enforced, and which




you chose not to enforce, and why. We will also provide a data.txt le with a plain text description of a database.




some queries




After you have nished schema.ddl dene a new le data.sql to populate an instance of your database




with sample data. We will not be autotesting this, so you get to decide on table structure and attribute names as you like. Write queries for the following:




For each dive category from open water, cave, or beyond 30 meters, list the number of dive sites that provide that dive type and have at least one monitor with booking privileges with them who will supervise groups for that type of dive.



Find monitors whose average rating is higher than that of all dives sites that the monitor uses. Report each of these monitor's average booking fee and email.



Find the average fee charged per dive (including extra charges) for dive sites that are more than half full on average, and for those that are half full or less on average. Consider both weekdays and weekends for which there is booking information. Capacity includes all divers, including monitors, at a site at a morning, afternoon, or night dive opportunity.



For each dive site report the highest, lowest, and average fee charged per dive.



Queries are stored in

les q1.sql, q2.sql, q3.sql, and q4.sql.




dependencies, decompositions, normal forms
le dependencies.pdf report the following:




1. Relation R has attributes IJKLMNOP and functional dependencies:




SP = fM ! IJL; J ! LI; JN ! KM; M ! J; KLN ! M; K ! IJL; IJ ! Kg




Find a minimal basis for R. List the attributes in each LHS in alphabetical order, each RHS in alphabetical order, and your entire set FDs in alphabetical order.



Find all keys for R.



Uses 3NF to nd a lossless, dependency-preserving decomposition of R. Be sure to combine FDs with the same LHS into the same into a single relation, and omit relations that are subsets of other relations.



Does your schema allow redundancy? Explain why, or why not.



Show all your intermediate steps, and explain any steps you may legitimately omit.




2. Relation T contains attributes CDEF GHIJ and functional dependencies:




ST = fC ! EH; DEI ! F; F ! D; EH ! CJ; J ! F GIg




Which of the FDs in ST violate BCNF?



Decompose T into a collection of relations that are each in BCNF, using the BCNF decomposi-tion algorithm. Your decomposition should be lossless and redundancy-preventing. Show your intermediate steps, explaining any steps that you may legitimately omit. Project ST onto your nal set of relations, where every LHS and RHS is in alphabetical order, and each list of projected FDs itself should be in alphabetical order.



submissions




Submit the following les on MarkUs well before 4:00 on March 27:




schema.ddl



data.sql



q1.sql, q2.sql, q3.sql, and q4.sql



dependencies.pdf



One submission per group, whether a group is one or two people. Declare your group by submitting an empty, or partial, le, and this should be done well before the due date. You may always replace such a

le with a better version, until the due date. We will not have much patience with problems forming groups at the last minute, since this indicates lack of seriousness in group work.




Double check that you have submitted the correct version of your les by downloading them from MarkUs.



marking



We assess schema.ddl and data.sql according to how well they represent the Wet World domain and the aims listed above. We will mark q1.sql, q2.sql, q3.sql, and q4.sql for correctness. In addition, we will mark the following style considerations:




clear and informative table, view and attribute names



comments for every view or table describing the rows of the table according to what the data means, rather than how to obtain it



80 character limit per line



view and table names start with a capital, CamelCase if they use multiple words



SQL keywords should either consistently lower-case or consistently all-caps



indentation to improve readability