Reference no: EM132249807

Task: Invoice to Database conversion

1. Select an Invoice from a company/service etc. You can search for it online or use one of your company or service providers (handyman, pool service etc). Invoice needs to have at least 4 different fields and at least 3 lines of data for most of the fields.

2. Attach the .jpeg or screen shot of the Invoice.

3. List all the possible fields you see on the invoice.

4. Prepare a database named ‘Invoice' and add all the fields as columns and fill up the table with given data(line items,name etc.).

5. Perform the Normalization process of 0NF, 1NF, 2NF, 3NF and normalize the Invoice table. This might lead to creation of other tables too. Normalize them too using same process. Show all your steps in an organized way.

6. Once the tables/entities have been identified → Draw the E-R model (ERD) using Visio 2013 and paste a screen shot of the E-R diagram. Mark the Primary Key (PK) (which can also be a composite key- so you mark two primary keys) and Foreign (FK) if applicable.

Assignment 1

Task: Build an Entity Relationship Model/Diagram for the following problem statement using Visio 2013.

Data for an information technology conference needs to be collected. The conference has a variety of sessions scheduled over a two-day period. All attendees must register for the sessions they plan to attend. Some speakers are presenting only one session, whereas others are handling multiple sessions. Each session has only one speaker.

The diagram should show the following:
1. Entities
2. Relationships using appropriate connectors(connectors should be connected in right angled form and not crossing over each other or entity boxes, also edit them and make lines thicker)
3. Primary Key attribute
4. Any Foreign Key attribute

Assignment 2

Task: Perform Database Normalization

1. Using Excel - Normalize the following Table using the rules 1NF and 2NF.

2. Highlight the Primary Key, Composite keys, Foreign Key of the Table(s).

Student Info

Student ID	Name	Email	Course No.	GradePoints
100111	John Doe	[email protected]	NN204, SI204, IT221	2,3,3
92244	Matt Smith	[email protected]	SM223, EE301	4,4
113221	Melinda Black	[email protected]	SI204	3
90110	Tom Johnson	[email protected]	NN204, SI204, IT221	4,2,3

3. Can you do 3NF normalization on this table ? Yes or No ...explain why ?

4. What ‘new meaningful attribute' can you add to the original table so 3NF normalization can be applied ?Deduce all the three forms 1NF, 2NF, 3NF again using that attribute in the original table.

5. Upload the completed Excel document on Canvas.

Assignment 3

Task: Database Creation (Store - imaginary store sells items such as books, videos, DVDs, CDs) and execute queries on it using Oracle SQL+

Instructions to follow:
1. Download the store_schema(SQL text file) from Canvas

2. Create the Storedatabase in Oracle by running the store_schema.sql
To do this on the SQL Prompt you type: StartPathname of SQL file

Get the Pathname of SQL file through Virtual apps. Search for the file through Virtual apps, File > open ...and not directly from your laptop/desktop

3. Once database has been created type and execute the following SQL statements.
NOTE: Open ‘Note Pad'- type your queries there and then cut & paste them to Oracle will be easier to edit them.

a) SELECT table_name
FROMuser_tables;

How many tables have been created in the ‘Store' database ?Paste the results screen shot of the Oracle screen.

b) SELECT *
FROM customers;

How customers are there in this table ?
Which customer is missing the Date of Birth ?

c) Write and execute the SQL to find out ‘all' the products being sold by this store (Hint: Query the product_types table). (0.5 points)
Paste SQL and results screen shot:

d) Write and execute the SQL to find out the name of the person who has the ‘title' as ‘sales manager' of the store. (0.5 points)

Paste SQL and results screen shot:

e) Write and execute the SQL to retrieve rows with combined first and last names of the customer from ‘customers' table. Remember there should be a space separating first with last name in the combined name. (0.5 points)

Paste SQL and results screen shot:

f) Write and execute the SQL to retrieve the ‘description' of the products in the Products table - under a changed heading in the query results. Hint: Use alias method.

Paste SQL and results screen shot:

g) Write and execute SQL on any of the tables in Store database using the following commands: For each of them explain the ‘business need'.
1. Any of the comparison operators
2. Any of the arithmetic operators
3. Any of the logical operators
4. Order By clause

Assignment 4

Task: Table Creation and Management

1. Complete the following questions. Perform DESC command on the tables in each question to validate your answers. Paste the screen shots of SQL and the DESC results.

a. Create a new table containing the category code and description for the categories of books sold by a store. The table should be called ‘category' and the columns should be ‘catcode' and 'catdesc'. The catcode column should store a maximum of 2 characters and the catdesc column should store a maximum of 10 characters.

b. Create a new table ‘employee' containing these four columns: emp#, lastname, firstname and job_class. The job_class column should be able to store character strings up to a maximum length of four but the column values shouldn't be padded if the value has less than four characters. The emp# column contains a numeric ID and should allow a five-digit number. Use column sizes you consider suitable for the firstname and lastname columns.

c. Add two columns to the ‘employee' table created in above question. One column named empdate will contain the date of employment for each employee and its default value should be the system date. The second column named enddate contains employee's date of termination.

d. Modify the job_class column of the employee table so that it allows storing a maximum width of two characters.

e. Delete the enddate column from the employee table.

f. Rename the employees table as jl_emps.

g. Create two new tables named ‘new1' and ‘new2' similar to any ‘two' of the current tables in the store_schema database. Use the same column names and data types.(Hint: Use AS (SELECT...) sql in the CREATE statement)).

Paste the DESC and SELECT * results of all the tables (existing ones and the new ones - so total of 4 tables) to verify their creation and having same data.

h. Perform the following functions on ‘new1' table created in above question. Paste screen shots of the SQL and DESC for each one of the following.

i. Mark one of columns as ‘unused'. Verify the column is no longer available.
ii. Truncate the table and then verify that the table still exists but no longer contains any data.

Attachment:- Class Assignments.zip