CS XXXXXXXXXXDatabase Design Homework 04 - SQL DDLDirections:Using the PhotoDB data model diagram and additional contextual information below, create a single .sql file with all necessary CREATE...

This is a SQL programming assignment working with databases. I attached the document with instructions and the assignment itself


CS 3200 - Database Design Homework 04 - SQL DDL Directions: Using the PhotoDB data model diagram and additional contextual information below, create a single .sql file with all necessary CREATE TABLE commands and any other necessary SQL commands. The name of the SQL file should be photodb.sql. This SQL file should be targeted to MySQL. You should also include necessary commands to create and use photodb database. Pay close attention to the database diagram. Your SQL file must match it exactly. Each of the icons next to attribute names has a particular meaning which you can ascertain from trial and error or DataGrip documentation. Additional information about constraints is mentioned below in the description of the tables. PhotoDB PhotoDB (see next page for diagram) is a database for a simple photo sharing web application that allows users to upload photos and tag other users in each photo. It consists of four tables: 1. Users - users of the system. a. Email addresses should not be duplicated in the system and every user must have an email address. b. The created_at attribute should be filled automatically with a timestamp that the information was added to the table. c. If a user deletes their account, any pictures they uploaded should be deleted and any tags of them in other users’ photos should be deleted as well. 2. Cameras - basic information about the various cameras used to take photos. Note that a camera is only related to pictures, not to an owner. a. If any pictures linked to a particular camera, that camera cannot be deleted until all associated pictures are deleted. 3. Pictures - the pictures being stored in the system. a. The location attribute is a user-entered value intended to store the geographical location where the picture was taken… “Boston, MA” or “Paris”, for example. b. The date_taken attribute’s value will be entered by the user of the system. c. If a picture is deleted from the system, any tags related to it should be deleted as well. 4. Tags - contains information about which users have been tagged in photos. a. A person cannot be tagged in a photo if they aren’t already a user in the system. b. The date_tagged attribute should be filled automatically with the time that the row is added to the database. General note: any update to values linking tables should be automatically changed in the referencing tables.