代写数据库SQL作业，练习创建数据表的语法。
Getting started
This assignment involves using SQL to create and populate database tables. It
is based on Chapter 6 of the MDM textbook. Review in-class handouts and
exercises. Please make sure to complete all of the required reading and review
in-class practice.
Problem description
The ERD on the next page presents a conceptual database model for the
following scenario:
A local conservatory would like to maintain a record of auditions, in which
juries select applicants for participating in different conservatory
ensembles. Each jury is qualified to judge auditions for one or more
instruments. Each applicant is assigned an audition for their chosen
instrument at a given date, time and location. A jury, upon listening to an
applicant during the audition, records an audition score (between 1 and 10)
and a recommendation regarding the placement of the applicant.
- Compose the SQL code (i.e., CREATE statements) to create the table structures based on the above scenario and ERD on the next page. Replace all composite keys with surrogate ones.
Use the following constraints, where appropriate:
* primary key,
* foreign keys and referential integrity constraints,
* uniqueness constraints on all non-PK candidate keys,
* value check constraints (constraints on time and date attributes are for extra credit only)
* default values. - Write SQL commands to populate the database with
* 3 different juries
* 5 different instruments (some, without an associated jury)
* 8 applicants
* 6 auditions - Write SQL code to drop a Foreign Key constraint you created in question 1 and to add it back.
- Write the SQL code to modify the length of one of the attributes you created in question 1
- Use an SQL Update command to modify all names of instruments to uppercase letters.
- To illustrate the constraints, for each of the four tables, include an INSERT statement that violates one constraint associated with the table, but is correct in all other respects. In a comment, explain which constraint is violated by this INSERT.
Submission and grading
Submit a. the code you composed for parts 1-6 above, and b. a listing of all
data in the tables (obtained via SELECT *) as a single file (with
extension.sql) via Blackboard prior to the deadline.
All code (except for the code for part 6) should be executable and should not
produce any errors when executed. Execution errors in the code will result in
the score of 0 for each failed statement.
Those solutions that were not submitted in time electronically will be
considered late and will incur 50% late penalty (see course policies). Hand in
a printed version in class. Not handing in a printer version will result in a
reduced grade and possibly a delay in grading.
