HOW TO CREATE A DATABASE AND ITS TABLES with SQL STATEMENTS

b. AND - And this is IMPORTANT - Write it in MYSQL (NOT ORACLE etc) I AM USING Murach's SQL Server 2019 For developers. Chap 11 has much of the information if you need reference. This problem here involves using Data Definition Language (DDL) and Data Manipulation Language (DML)

PROBLEM OVERVIEW: This is a MYSQL 101 Course and is a project to give me a bit of experience in designing and implementing a database application. I'm using MS SQL Server / and draw.io.

NOTE: This problem here involves using Data Definition Language (DDL) and Data Manipulation Language (DML) to take the PART 1 - Spreadsheets provided, PART 2. THEN - Normalize them, 3. THEN CREATE AN EERD from the SPreadsheets and Normalization Diagram using the business rules provided below.

Where I need help - WITH ALL OF THIS - the next step is to create the DDL/DML Language necessary to "CREATE TABLE" and "INSERT INTO" statement/queries that you would use in MS SQL Server.

(THE NEXT STEP I WILL TAKE AFTER THIS IS TO RUN SQL QUERIES AGAINST THE DATABASE THAT HAS BEEN CREATED. MSSQL QUestion Using M*SOFT SQL SERVER STUDIO: What I am trying to figure out is how, using DDL&DML Language to - PROBLEM: a. "CREATE TABLE" and b. "INSERT INTO" statements based upon the following: (Please note there are Four Sets of information below if needed)

A. My EERD Diagram looks like this:

B. The two (2) original Spreadsheets look as follows:

C. BUSINESS RULES:

• A Paid Employee is either a Contractor or works directly for the zoo, but can’t be both.
• Everyone is assigned a department, and assigned to a building or enclosure.

D. NORMALIZATION DIAGRAMS:

And then for the zoo and animals

You may find that I need to provide some other peice of information. IF YOU FIND THAT TO BE THE CASE:

Please, Please, PLease BE SPECIFIC AS TO WHAT YOU NEED TO WRITE THE CODE IN DDL/DML
 
 
 
 
 

Transcribed Image Text:

PK Animal Name Gender Lives In # Zoo PK Z_ID Name Location ||+++ -Type Of H Animal Type Type Enclosure Regular Employee PK R-ID -Works In- FK C_ID FK C_ID FK Z_ID FK B_ID Paid Employee PK SSN E_ID E_FName E_LName Contact State Enclosure Assigned, -0€ Email Address D_ID Works Under |+ Contractor PK C_ID FK B_ID -Work In Assigned Assigned -Belongs To- ||++ Assigned |++ Work In Department PK D_ID Name Row 2 +H PK Zip City PK D_ID B_ID Name Building Name # PK Animal Name Gender Lives In # Zoo PK Z_ID Name Location ||+++ -Type Of H Animal Type Type Enclosure Regular Employee PK R-ID -Works In- FK C_ID FK C_ID FK Z_ID FK B_ID Paid Employee PK SSN E_ID E_FName E_LName Contact State Enclosure Assigned, -0€ Email Address D_ID Works Under |+ Contractor PK C_ID FK B_ID -Work In Assigned Assigned -Belongs To- ||++ Assigned |++ Work In Department PK D_ID Name Row 2 +H PK Zip City PK D_ID B_ID Name Building Name # PK Animal Name Gender Lives In # Zoo PK Z_ID Name Location ||+++ -Type Of H Animal Type Type Enclosure Regular Employee PK R-ID -Works In- FK C_ID FK C_ID FK Z_ID FK B_ID Paid Employee PK SSN E_ID E_FName E_LName Contact State Enclosure Assigned, -0€ Email Address D_ID Works Under |+ Contractor PK C_ID FK B_ID -Work In Assigned Assigned -Belongs To- ||++ Assigned |++ Work In Department PK D_ID Name Row 2 +H PK Zip City PK D_ID B_ID Name Building Name # PK Animal Name Gender Lives In # Zoo PK Z_ID Name Location ||+++ -Type Of H Animal Type Type Enclosure Regular Employee PK R-ID -Works In- FK C_ID FK C_ID FK Z_ID FK B_ID Paid Employee PK SSN E_ID E_FName E_LName Contact State Enclosure Assigned, -0€ Email Address D_ID Works Under |+ Contractor PK C_ID FK B_ID -Work In Assigned Assigned -Belongs To- ||++ Assigned |++ Work In Department PK D_ID Name Row 2 +H PK Zip City PK D_ID B_ID Name Building Name #

Answer rating: 100% (QA)

Based on the provided Enhanced EntityRelationship Diagram EERD and the normalization diagrams we can create the SQL Data Definition Language DDL statements to define the structure of the database tabl... View the full answer