SQL

In this lab assignment, you will develop a database for an Archaeology museum that stores and displays historic artifacts. Each artifact is identified by an artifact number (e.g., CL892), and has its dimensions and weight. Each artifact is made of a specific material clay, glass, bronze, fabric, gold, etc., and has a recommended optimal storage humidity. A number of conservation and restorations technicians work at the museum. You need to store the name, Social Insurance Number (SIN), address, phone number, and salary of each technician, and specify if she or he is conservationist or restorer, or both. Each technician is an expert on one or more artifact materials (e.g. silver and gold), and his or her expertise may overlap with that of other technicians. This information about technicians must also be recorded. There are also museum guides (who are, essentially, educators) working at the museum. Museum guides must have a bi-annual examination to ensure their knowledge is up-to-date. For each guide, you must store the date of the most recent exam. All museum employees (including technicians and guides) belong to a union. You must store the union membership number of each employee. You can assume that each employee is uniquely identified by the Social Insurance Number. The museum conducts a number of tests that are used periodically to ensure that artifacts are stored properly. Each test has a Ministry of Culture test number, a name, and a maximum possible score. The Ministry requires the museum to keep track of each time that a given artifact is inspected by a given technician using a given test. For each testing event, the information needed is the date, the number of hours the technician spent doing the test, and the score that the artifact storage conditions received on the test. More specifically, you should accomplish the following tasks:

1. Design a database schema to model the domain of the museum as described above. You can use as many relations as you deem necessary. Determine suitable data types for each attribute. Specify the keys (and foreign keys, if needed) for relations. It is up to you to decide if there should be any keys, and which attribute (set of attributes) should form a key. Provide the definition of the schema in a simple text file named LastNameFirstNameSchemaDefini- tion.txt, where each relation is defined in the form:

RelationName(attribute_1 datatype, attribute_2 datatype, ..., attribute_7,...) Keys of RelationName: {attribute_1, attribute_2}, {attribute_7}...

After defining the schema, include in the file LastNameFirstNameSchemaDefinition.txt a short paragraph explaining the schema you created. Focus on explaining what information each of the relations holds, how you chose the data types of the attributes, and how you picked the key constraints. The paragraph should be no longer than 200 words.

2. Write the data definition commands for creating the schema you chose. The commands should generate the tables, as well as the key constraints.

3. Insert at least 10 records into your database using the INSERT INTO command. Include the commands for tasks 2 and 3 in a text file named LastNameFirstNameSchema.txt. You should submit both LastNameFirstNameSchemaDefinition.txt and LastNameFirstNameSchema.txt.