affogarty / comp-421-databases-term-project Goto Github PK

Refs #5

We need to include the relational schema from A1. It needs to include any changes.

Refs #6

Refs #5

Refs #8

Refs #3

Refs #6

The TA said "staff should only work for one department". How do we want to handle this? Should we just merge WorksFor() with Staff()?

Refs #27

We need to update the relational schema and fix any problems from A1.

Refs #1

Refs #3

Refs #6

Refs #5

Refs #3

Refs #3

Write an SQL database schema for the relational schema you have designed using the CREATE TABLE command and enter it in the DB2 database. Choose suitable data types for your attributes. Indicate primary keys, foreign keys or any other integrity constraints that you can express with the commands learnt. Indicate the constraints you cannot express. The Online Information contains detailed information about data types, and the CREATE TABLE statement.

Turn in your CREATE TABLE statements. Furthermore, (i) for DB2 use command line processor command DESCRIBE tablename (ii) for PostgreSQL use table name (prints the description of the relation on the screen) for each of your relations, print the result and turn it in.

Create two views on top of your database schema.

Turn in an informal description what data each of the views represents, show
your CREATE VIEW statements and the response of the system. Also, show
a query involving each view and the system response (but truncate the
response if there are more than a few tuples produced.

The TA thinks that InChargeOf means that the patient is in charge of the department, and not the other way around. Perhaps we should give it a new name?

Refs #2

Execute five INSERT commands to insert tuples into one of your relations.
Turn in your INSERT statements. Furthermore, print and turn in the
response of CPL/psql when you type the INSERT commands. Print and turn
in the result when you issue a SELECT * FROM relationname command.

Refs #7

Write four data modification commands for your application. Most of
these commands should be \interesting," in the sense that they involve some
complex feature, such as inserting the result of a query, updating several tuples at
once, or deleting a set of tuples that is more than one but less than all the tuples in
a relation.

Turn in a description of all the relations that you use in your modifications
but are not described so far. Provide a short description of what each of your
statements is supposed to do, the SQL statements themselves and a script or
screenshot that shows your modification commands running in a convincing
fashion.

Refs #6

Refs #8

Refs #7

Write five queries on your project database, using the select-from-where construct of SQL. The queries should be typical queries of the application domain. To receive full credit, all but perhaps one of your queries should exhibit some interesting feature of SQL: queries over more than one relation, subqueries, aggregations, grouping etc.

Turn in a description of all the relations that you use in your queries (e.g., the original create statements or printouts from the SQL \describe table yourname" function), a description of what each of your queries is supposed to do, the SQL statement of each query, along with a script illustrating their execution (for example the screenshot when you execute the query). Your script should be su_cient to convince us that your commands run successfully. Please do not, however, turn in query results that are more than 50 lines long.

Refs #5

Insert in all your tables enough meaningful information so that the
queries that you create provide meaningful results. The results of the following
queries that you develop should have a reasonable number of results so that we
can be convinced that your queries are correct (maybe 5-10 tuples). If you have
real-world data, feel free to import it. Information of how to import data into DB2
tables is provided on my courses.

For each table show the output, truncated to the first 5-10 tuples, that are
returned when you issue a SELECT * FROM relationname command.

Refs #3

Add two CHECK constraints to relations of your database schema.

Turn in the revised schema, its successful declaration, and the response of
DB2 to modifications (insert/update) that violate the constraints.

Refs #5