APAN 5400 - Managing Data - Assignment 3 - Advanced SQL Queries

Notes

发布日期: 2021-02-04

作业标题： APAN 5400 - Assignment 3 Advanced SQL Queries
课程名称：C-lumbia University APAN 5400 Managing Data
完成周期：1天

In this assignment, you will create a relational database using the specifications stated in ordinary English. You will also populate the database using the INSERT command in SQL. This will give you an opportunity to practice your knowledge of SQL as a Data Definition Language. It will also give you an opportunity to demonstrate how well you understand constraints on the data and how to represent them in creating your database.
This assignment supports the following objectives:

Create a relational database containing at least three tables
Populate the database using the INSERT command
Create integrity constraints on the tables created

1. Details

Columbia Deli has hired you as a consultant to design a database for the deli. They have provided you with the following information:

Every employee has a social security number, name, salary, and date of hire.
The deli is organized into several departments. Each department has a unique name.
Each department has exactly one employee as its manager but an employee may manage more than one department. The database should record the date when the most recent manager of each dept. was appointed in that position.
Every employee must be assigned a department.
Every employee has a supervisor who need not be the manager of any department.

The deli has these five departments:

hot foods
sandwich
snacks
beverages
training

You are provided the following additional information, which should be inserted into the database using SQL queries or Python Code (not using any GUI interfaces):

Jim Jones (ssn: 134-56-8877, salary: $28,000, dept:hot foods, date of hire: 1/26/2015, supervisor: Rita Bita)
Rita Bita (ssn: 138-56-8050, salary: $32,000, dept: beverages, date of hire: 2/15/2017, supervisor: Holly Dew. manages: beverages, starting 3/18/2018)
Holly Dew (ssn: 334-55-8877, salary: $29,000, dept:sandwich, date of hire: 1/15/2016, supervisor: Pablo Escobar)
Pablo Escobar (ssn: 666-56-6666, salary: $48,000, dept:snacks, date of hire: 1/26/2014, supervisor: Rita Bita, manages: snacks, starting 5/5/2015)
Al Capone (ssn: 888-91-8870, salary: $40,000, dept:hot foods, date of hire: 1/26/2015, supervisor: Pablo Escobar, manages: hot foods, starting 1/1/2016)
Bonnie Clyde (ssn: 111-22-3333, salary: $42,000, dept: sandwiches, date of hire: 4/7/2015, supervisor: Al Capone, manages: sandwich, starting 1/1/2016)
Tom Horn (ssn: 456-11-3883, salary: $45,000, dept: training, date of hire: 5/22/2013, supervisor: Al Capone, manages: training, starting 2/22/2012)
Charles Keating (ssn: 428-59-3418, salary: $47,832, dept: snacks, date of hire: 7/28/2019, supervisor: Rita
Bita, manages: training, starting 9/29/2019)

1.1. Helpful Hints:

Please do not assume that the database should be structured in the way this data is provided. Your database should be structured such that constraints such as “Each dept. has exactly one employee as a manager” can be enforced.
There should not be a separate table for Deli – the entire database represents the deli. There should be a table for each major entity mentioned in the specifications. Relations between entities (such as, who works in which department) should be their own tables. Primary keys for each table should be chosen to enforce constraints.

2. Assessment

Students will need to read the readings specified in the resources and look closely at the examples given in the slides.

In assessing this assignment we will look closely at whether or not students have mastered the basic templates for creating tables and modifying the content of the database (insert, delete, and update). We will also look at whether they know how to set up referential integrity constraints correctly.
A common pitfall in designing a database is to create just one table and insert all the information into this table. Please avoid that. The design of the database should be such that the constraints can be enforced to the extent within the DBMS as opposed to external code.

Important Note: Screen shots
Students must also submit screen shots of their Postgres/pgAdmin database interactions. Students submitting without screen shots will lose 20% of the points they earned.