Assignment-1_updated
docx
keyboard_arrow_up
School
University of South Florida *
*We aren’t endorsed by this school
Course
6218
Subject
Information Systems
Date
Jan 9, 2024
Type
docx
Pages
2
Uploaded by UltraRiverPrairieDog40
ASSIGNMENT 1:
Task:
Build the “Physical” design of this data model (“Logical” design). Datasets:
You will be assigned one of the following datasets below. DS1
: Grocery Store
Fruits: It is possible to have fruits that are not sold in a grocery store. However, Grocery stores must sell at least one type of fruit.
Download Grocery store data here: https://catalog.data.gov/dataset/grocery-stores-fb200
Download Fruits data here: https://www.ers.usda.gov/data-products/fruit-and-vegetable-
prices/
(Note: Choose the ALL FRUITS – Average prices (CSV format) dataset). DS2
: Traffic Violations
Drivers: It is possible to have drivers which have never experienced a traffic violation. However, all traffic violations must have a driver associated with them. Sometimes a driver may have more than one traffic violation.
Download the traffic violation data here: https://catalog.data.gov/dataset/traffic-violations
Create your own “Drivers” entity with your columns of choice. DS3
: Real Estate Property Sales
Buyer; To keep this scenario simple, consider the following: Buyers can purchase one or more real estate properties. But, in this data set a Real Estate Property can only be sold to one buyer.
Download Real Estate Sales data here: https://catalog.data.gov/dataset/real-estate-sales-2001-
2018
Create your own “Buyers” entity with your columns of choice. DS4
: NYC Jobs
Job Applicant Hired; A dataset contains job openings that were available on the City of New York’s official jobs site. Internal postings are available to city employees and external postings are available to the general public. Each job posting may have one or more available positions. But, only one applicant can occupy one position if they are hired.
NYC jobs data can be downloaded here: https://catalog.data.gov/dataset/nyc-jobs
Create your own “Job Applicant Hired” entity that includes all applicants that have been hired for a posted NYC position. Requirements:
Create a database using Microsoft SQL Server. Name the database appropriately. Create the entities described in your datasets. Add appropriate Foreign Keys (FK). Now add (
INSERT
) 10 rows of data in each of the entities (tables). Write a SELECT
statement to confirm the results of both tables. Assignment Deliverables
: (
4 files
for submission: .sql. (2 of these), .docx, and .pdf) Create a separate .sql
file with your CREATE TABLE statements and upload to the assignment in Canvas. Create a separate .sql
file with your INSERT and SELECT statements and upload to the assignment in Canvas. Create a MS Word document
, describe in several sentences the “
Cardinality
” and “
Modality
” of this database design (model). Upload the MS Word document to the same assignment in Canvas. Use LucidChart to build your data model diagram. Export the diagram from LucidChart as a PDF
and upload to this assignment in
Canvas.
Assignment Rubric
: Total = 30 Points
10 Points: .sql file must include the physical design with your CREATE TABLE statements. The CREATE TABLE statements must be complete to include all tables, primary keys, foreign keys. 10 Points: .sql file must include at least 10 INSERT statements and a 1 SELECT Statement for each Table.
5 Points: .pdf file must include the ERD for your data model 5 Points: .docx file must clearly describe the “Cardinality” and “Modality” of your database model (ERD).
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help