DSC 450: Database Processing for Large-Scale Analytics Assignment Module 2

$30.00

Original Work ?
Category: You will Instantly receive a download link for .ZIP solution file upon Payment

Description

5/5 - (5 votes)

Part 1

  • Using your logical schema from previous assignment (Part 2-a), write the necessary SQL DDL script to create the tables. Be sure to specify every primary key and every foreign key. Make reasonable assumptions regarding the attribute domains (for example, setting every column to VARCHAR2(100) is not reasonable).

 

  • Write SQL INSERT statements to populate your database from Part 2-a with the following data (NOTE: remember that strings would need to use single quotes, e.g., ‘Asimov’). Be sure to verify that your statements worked correctly and loaded the data in Oracle (see tutorial on how to connect to Oracle using SQLDeveloper).

 

  1. (King, Stephen, 2, September 9 1947)
  2. (Asimov, Isaac, 4, January 2 1921)
  3. (Verne, Jules, 7, February 8 1828)
  4. (Rowling, Joanne, 37, July 31 1965)

 

  1. (Bloomsbury Publishing, 17, London Borough of Camden)
  2. (Arthur A Levine Books, 18, New York City)

 

  1. (1111-111, Databases from Outer Space, 17)
  2. (2222-232, Revenge of SQL, 17)
  3. (3333-323, The Night of the Living Databases, 18)

 

  1. (2, 1111-111, 1)
  2. (4, 1111-111, 2)
  3. (4, 2222-232, 2)
  4. (7, 2222-232, 1)
  5. (37, 3333-323, 1)
  6. (2, 3333-323, 2)

 

  • Using logical schema from previous assignment (Part 2-b) write the necessary SQL DDL script to create the tables. Be sure to specify every primary key and every foreign key.

 

  • Write a python function to validate SQL insert statements. The function will take a string containing a SQL insert statement and print two kinds of messages. 1) “Invalid insert” or 2) Inserting [list of values] into [the target] table. The values and the table name would be based on each particular statement. For validating the statement, you only have to check that the insert statement starts with INSERT INTO and that statements ends with a semicolon (;).

 

Examples:

validateInsert(”INSERT INTO Students VALUES (1, ‘Jane’, ‘A+’);”)

  • Inserting (1, ‘Jane’, ‘A+’) into Students table

validateInsert(“INSERT INTO Students VALUES (1, ‘Jane’, ‘A+’)”)

  • Invalid insert

validateInsert(“INSERT Students VALUES (1, ‘Jane’, A+);”)

  • Invalid insert

validateInsert(“INSERT INTO Phones VALUES (42, ‘312-555-1212’);”)

  • Inserting (42, ‘312-555-1212’) into Phones table

 

Part 2

 

Consider a MEETING table that records information about meetings between clients and executives in the company.  Each record contains the names of the client and the executive’s name as well as the office number, floor and the building.  Finally, each record contains the city that the building is in and the date of the meeting.  The table is in First Normal Form and the primary key is (Client, Office).

(Date, Client, Office, Floor, Building, City, Executive)

 

You are given the following functional dependencies:

Building → City

Office → Floor, Building, City

Client → Executive

Client, Office → Date

 

  1. For the functional dependency Building → City, explain the redundancy problem and possible consequences through an example (you can make up your own building names as you see fit).

 

  1. Remove any existing partial dependencies and convert the logical schema to the Second Normal Form.  Please remember that when performing schema decomposition you need to denote primary key for every new table as well as the foreign key that will allow us to reconstruct the original data.

 

  1. Remove any existing transitive dependencies to create a set of logical schemas in Third Normal Form.  Again, remember to denote primary keys and foreign keys (including which primary key those foreign keys point to).

 

Part 3

 

Consider a table that stores information about students, student name, GPA, honors list and the credits that the student had completed so far.

 

(First, Last, GPA, Honor, Credits)

 

You are given the following functional dependencies

 

First, Last → GPA, Honor, Credits

GPA → Honor

 

  1. Is this schema in Second Normal Form?  If not, please state which FDs violate 2NF and decompose the schema accordingly.

 

  1. Is this schema in Third Normal Form?  If not, please state which FDs violate 3NF and decompose the schema accordingly.