CSC 455: Database Processing for Large-Scale Analytics

Assignment 1 – Alex Chou

 

Part 1

a)Create a relational schema with underlined (primary) keys and arrows connecting foreign keys and primary keys for a database containing the following information. If you have any difficulty drawing arrows, you can write foreign key information in a sentence instead.

•       Authors have Last Name, Firstname, ID, and Birthdate (identified by ID)

•       Publishers have Name, ID, address (identified by ID)

•       Books have ISBN, Title, Publisher (each book has a unique publisher and can be identified by ISBN).

•       Authors Write Books; since many authors can co-author a book, we need to know the rank of an author contributing to a book, stored in this table (i.e. a number 1, 2, 3; for single author books, this number is 1).

NOTE: Part 2 has some sample data which may be helpful.

b)Create a relational schema for students and student advisors

ï‚·Students have First Name, Last Name, DOB, Telephone and a reference to their advisor

ï‚·Advisors have ID, Name, Address, Research Area

Red circles are Primary key, and Blue circles are Foreign Keys

 

 

Part 2

 

1)      Using your logical schema from Part1-a, write the necessary SQL DDL script to create the tables. Be sure to specify every primary key and every foreign key. You can make reasonable assumptions regarding the attribute domains

 

 

Creat Table Enrollment

(

ISBN VARCHAR2(100),

Title VARCHAR2(100),

Publisher VARCHAR2(100),

AuthorIDVARCHAR2(100),

PublisherIDVARCHAR2(100),

 

Constraint E_PK

                        Primary Key (ISBN),

Constraint E_FK1

                        Foreign Key (AuthorID)

                                    Reference Author(AuthorID),

Constraint E_FK2

                        Foreign Key (PublisherID)

                                    Reference Publisher (PublisherID),

);

 

 

2)      Using logical schema from Part1-b write the necessary SQL DDL script to create the tables. Be sure to specify every primary key and every foreign key. For Students table, clearly state the assumptions you have made when choosing a primary key. You can make reasonable assumptions regarding the attribute domains.

 

Creat Table Enrollment

(

ID VARCHAR2(100),

NAME VARCHAR2(100),

Address VARCHAR2(100),

Research VARCHAR2(100),

Reference VARCHAR2(100),

 

Constraint E_PK

                        Primary Key (ID),

Constraint E_FK1

                        Foreign Key (Reference)

                                    Reference Student(Reference),

);

 

3)      Write SQL INSERT statements to populate your database from Part1-a with the following data (NOTE: remember that strings would need to use single quotes, e.g., 'Asimov')

 

 

a)      INSERT INTO Author VALUES (‘King’, ‘Stephen’, ‘2’, ‘September 9 1947’) ;

b)      INSERT INTO Author VALUES(‘King’, ‘Stephen’, ‘2’, ‘September 9 1947’);

c)      INSERT INTO Author VALUES(‘Asimov’, ‘Isaac’, ‘4’, ‘January 2 1921’) ;

d)     INSERT INTO Author VALUES(‘Verne’, ‘Jules’, ‘7’, ‘February 8 1828’) ;

e)      INSERT INTO Author VALUES(‘Rowling’, ‘Joanne’, ‘37’, ‘July 31 1965’);

 

f)       INSERT INTO Publisher VALUES(‘Bloomsbury Publishing’, ‘17’, ‘London Borough of Camden’);

g)      INSERT INTO Publisher VALUES(‘Arthur A. Levine Books’, ‘18’, ‘New York City’);

 

h)      INSERT INTO Books VALUES(‘1111-111’, ‘Databases from outer space’, ‘17’);

i)        INSERT INTO Books VALUES(‘2222-222’, ‘Dark SQL’, ‘17’);

j)        INSERT INTO Books VALUES(‘3333-333’, ‘The night of the living databases’, ‘18’);

 

k)      INSERT INTO Books VALUES(‘2’, ‘1111-111’, ‘1’);

l)        INSERT INTO Books VALUES(‘4’, ‘1111-111’, ‘2’);

m)    INSERT INTO BooksVALUES(‘4’, ‘2222-222’, ‘2’);

n)      INSERT INTO BooksVALUES(‘7’, ‘2222-222’, ‘1’);

o)      INSERT INTO BooksVALUES(‘37’, ‘3333-333’, ‘1’);

p)      INSERT INTO BooksVALUES(‘2’, ‘3333-333’, ‘2’);

 

Part 3

 

importsqldata

conn = sqldata.connect('testdata.db')

print "Opened the database successfully";

 

importsqldata

conn = sqldata.connect('testdata.db')

print "Opened the database successfully";

conn.execute('''CREATE TABLE Students

       (ID INT PRIMARY KEY     NOT NULL,

       NAME TEXT    NOT NULL,

       GRADE CHAR(50));''')

print "Table is created successfully";

conn.close()

 

 

importsqldata

conn = sqldata.connect('testdata.db')

print "Opened the database successfully";

conn.execute("INSERT INTO Students (ID,NAME,GRADE) \

      VALUES (1, 'Jane', 'A' )");

conn.execute("INSERT INTO Students (ID,NAME,GRADE) \

      VALUES (2, 'Allen', 'A')");

conn.execute("INSERT INTO COMPANY (ID,NAME,GRADE) \

      VALUES (4, 'Mark', 'B')");

conn.commit()

print "Records are created successfully";

conn.close()

 

importsqldata

conn = sqldata.connect('testdata.db')

print "Opened the database successfully";

cursor = conn.execute("SELECT id, name,grade from Students")

for row in cursor:

print "ID = ", row[0]

print "NAME = ", row[1]

print "GRADE = ", row[2], "\n"

print "The Operation done successfully";

conn.close()

 

 

Part 4

 

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

 

a.      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). 

 

b.      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.

 

c.       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).

 

 

2NF

OfficeTable→ Office, Floor, Building, City

ClientTable → Client, Executive

Client, Office → OfficeTable, ClientTable, Date

 

3NF

BuildingTable → Building, City

OfficeTable → Office, Floor, BuildingTable, City

ClientTable → Client, Executive

ClientAndOfficeTable → ClientTable, OfficeTable, Date

 

 

 

Part 5

 

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

 

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

 

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

 

 

2NF

StudentNameTable→ First, Last, GPATable, Credits

GPATable → GPA, Honor

 

3NF

FirstNameandLastNameTable→First, Last

StudentNameTable→ FirstNameandLastNameTable, GPATable, Credits

GPATable → GPA, Honor