Assignment Background and Requirements This assignment uses a new database instead of what we've been using for the past assignments. A new database will be used for the remainder of the assignments. 1. For Assignment 4 you will be required to build the entire database as defined in the supplied ERD and should meet the requirements of the assumptions provided below. All database work must be done in the vLab. 2. You will create the entire database and its tables, relationships, and constraints in the database. 3. All tables, constraints, and stored procedures must be coded. Your will create your own stored procedures and must submit your code as part of this assignments prompt. I recommend copying and pasting your code into a text document and uploading them that way, this also create a backup for you as well just in case. Implementation 1. Create tables defined in the provided database documentation (table descriptions & ERD). 2. Column requirements must be implemented based on the requirement stated. 3. All tables must have a primary key. 4. All tables must be related to at least one other table. 5. All tables that have foreign keys must have the appropriate constraint defined and enforced. 6. Your table creation stored procedure must be named as the following: A4CreateTables 7. Foreign key constraints must be defined in a separate stored procedure, and must exist as an alteration of the tables that were created with the above stored procedure. This stored procedure must be named as: A4Create Relationships 8. Additional constraints must be defined in a separate stored procedure, and must exist as an alteration of the tables that were created with the stored procedure from above. This stored procedure must be named as: A4Create Constraints 9. Another set of stored procedures must be created to drop tables, constraints, and foreign keys. They should be named as the following: A4DropConstraints, A4Drop Relationships, A4Drop Tables 10. Comments identifying the different table levels must exist in your stored procedures. 11. Column names must NOT have the following: contain spaces, be in all capital letters, or use special characters. C Focus FO m the Internet can contain viruses. Unless you need to edit it's safer to stay in Protected View. Enable Editing tables that were created with the stored procedure from above. This stored procedure must be named as: A4CreateConstraints 9. Another set of stored procedures must be created to drop tables, constraints, and foreign keys. They should be named as the following: A4DropConstraints, A4DropRelationships, A4Drop Tables 10. Comments identifying the different table levels must exist in your stored procedures. 11. Column names must NOT have the following: contain spaces, be in all capital letters, or use special characters. It is recommended that you follow the naming schema seen in the book and lecture. Database Assumptions/Requirements 1. Media items can vary: individual music tracks/songs, albums/CD's, movie, or television show. 2. Media items can be rated using a standard rating: G, PG, PG-13, R, etc. 3. Media time length can be stored in hours, minutes, or seconds. 4. Media may have the same title and differ by release year. 5. A media item is identified by a Media Identifier. 1 6. A user's information is stored as an account. Information includes an account number, account holder name, email, and password. 7. Each account will have a start and end date for the subscription. 8. Each account number is alpha-numeric. 9. Each account can have multiple profiles that can be assigned to the account holder or other people. 10. Each profile can have multiple media lists, and each media list can then contain multiple media items. Media > PK Me dialdentifier A MediaCategory Title PK CategoryID Release Yr A Rating Name LAO Media Type Me dalength Account Profiles Media Played PK AcctProfile ID + PK Account Profile Name PK EFX Medialdentifier Account Number User PK Account Number Account HolderName Email Password Subscription Start SubscriptionEnd Assignment Background and Requirements This assignment uses a new database instead of what we've been using for the past assignments. A new database will be used for the remainder of the assignments. 1. For Assignment 4 you will be required to build the entire database as defined in the supplied ERD and should meet the requirements of the assumptions provided below. All database work must be done in the vLab. 2. You will create the entire database and its tables, relationships, and constraints in the database. 3. All tables, constraints, and stored procedures must be coded. Your will create your own stored procedures and must submit your code as part of this assignments prompt. I recommend copying and pasting your code into a text document and uploading them that way, this also create a backup for you as well just in case. Implementation 1. Create tables defined in the provided database documentation (table descriptions & ERD). 2. Column requirements must be implemented based on the requirement stated. 3. All tables must have a primary key. 4. All tables must be related to at least one other table. 5. All tables that have foreign keys must have the appropriate constraint defined and enforced. 6. Your table creation stored procedure must be named as the following: A4CreateTables 7. Foreign key constraints must be defined in a separate stored procedure, and must exist as an alteration of the tables that were created with the above stored procedure. This stored procedure must be named as: A4Create Relationships 8. Additional constraints must be defined in a separate stored procedure, and must exist as an alteration of the tables that were created with the stored procedure from above. This stored procedure must be named as: A4Create Constraints 9. Another set of stored procedures must be created to drop tables, constraints, and foreign keys. They should be named as the following: A4DropConstraints, A4Drop Relationships, A4Drop Tables 10. Comments identifying the different table levels must exist in your stored procedures. 11. Column names must NOT have the following: contain spaces, be in all capital letters, or use special characters. C Focus FO m the Internet can contain viruses. Unless you need to edit it's safer to stay in Protected View. Enable Editing tables that were created with the stored procedure from above. This stored procedure must be named as: A4CreateConstraints 9. Another set of stored procedures must be created to drop tables, constraints, and foreign keys. They should be named as the following: A4DropConstraints, A4DropRelationships, A4Drop Tables 10. Comments identifying the different table levels must exist in your stored procedures. 11. Column names must NOT have the following: contain spaces, be in all capital letters, or use special characters. It is recommended that you follow the naming schema seen in the book and lecture. Database Assumptions/Requirements 1. Media items can vary: individual music tracks/songs, albums/CD's, movie, or television show. 2. Media items can be rated using a standard rating: G, PG, PG-13, R, etc. 3. Media time length can be stored in hours, minutes, or seconds. 4. Media may have the same title and differ by release year. 5. A media item is identified by a Media Identifier. 1 6. A user's information is stored as an account. Information includes an account number, account holder name, email, and password. 7. Each account will have a start and end date for the subscription. 8. Each account number is alpha-numeric. 9. Each account can have multiple profiles that can be assigned to the account holder or other people. 10. Each profile can have multiple media lists, and each media list can then contain multiple media items. Media > PK Me dialdentifier A MediaCategory Title PK CategoryID Release Yr A Rating Name LAO Media Type Me dalength Account Profiles Media Played PK AcctProfile ID + PK Account Profile Name PK EFX Medialdentifier Account Number User PK Account Number Account HolderName Email Password Subscription Start SubscriptionEnd