Description

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. Quali ed divers will use the app to book dives with a dive monitor and dive site.
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.
schema
De ne a schema in DDL, stored in le schema.ddl. Your goals, in priority order, are:
1. Allow as few redundancies as possible.
2. Allow as few NULL or DEFAULT values as possible.
3. 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:
De ne a primary key for each table, and in addition use UNIQUE and de ne 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 de ne 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:
1. 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.
2. 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.
3. 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.
4. 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
In 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
(a) 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.
(b) Find all keys for R.
(c) 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.
(d) Does your schema allow redundancy? Explain why, or why not.
2. Relation T contains attributes CDEFGHIJ and functional dependencies:
ST = fC ! EH;DEI ! F;F ! D;EH ! CJ;J ! FGIg
(a) Which of the FDs in ST violate BCNF?
submissions
schema.ddl data.sql q1.sql, q2.sql, q3.sql, and q4.sql dependencies.pdf
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