Good Afternoon Everyone!

I was wondering if I could get some help with these computer science database principles. Thank you in advance for the assistance!

 

 

NORMALIZATION AND SQL DDL STATEMENTS
Case Assignment
In this assignment, you are expected to accomplish the following: Discuss what normalization is and why it is important. Normalize the database design in Module 2, and describe whether the tables are all normalized.
Explain whether the tables satisfy the requirements of 1NF, 2NF, and 3NF. If you believe your tables as submitted in Case 2 are already normalized, explain in your paper
how those tables meet the normalization standards. If not, how you would modify the design? Draw or describe your new database ERD the same way you did in Module 2 in the paper. Describe (in at least one paragraph) what ACID is and why it is important for databases. Describe (in at least a half page) why normalization is needed and how to optimize it. Describe
lessons learned from this exercise. Include in the paper the SQL statements used for creating tables and screenshots of the results in your
chosen DBMS.
Assignment Expectations Normalize tables in a database design. Be able to use SQL to create tables and indexes. Briefly discuss the pros and cons of normalization. Tables in an Electronics Store The Electronic Store data base is composed of three tables: Customer, Employee and SalesOrder. The
attributes of these tables are shown as below: SQL> create table Employee (SocialSecurityNumbernumber(5) primary key, FName varchar2(15), LName
varchar2(15), AddressLine1 varchar2(50), AddressLine2 varchar2(15), city varchar2(15),state varchar2(3),
phone varchar2(15), email varchar2(15), zipcode varchar2(15));
Output: Table created. SQL> desc Employee; Output:
Name Null? Type ----------------------------------------- -------- ----------------------------------------------SOCIALSECURITYNUMBER NOT NULL FNAME NUMBER(5)
VARCHAR2(15) LNAME VARCHAR2 (15) ADDRESLINE1 VARCHAR2 (50) ADDRESLINE2 VARCHAR2 (15) CITY VARCHAR2 (15) STATE VARCHAR2 (3) PHONE VARCHAR2 (15) ZIPCODE VARCHAR2 (15) EMAILID VARCHAR2 (15) SQL> create table Employee (SocialSecurityNumbernumber(5) primary key, FName varchar2(15), LName
varchar2(15), AddressLine1 varchar2(50), AddressLine2 varchar2(15), city varchar2(15),state varchar2(3),
phone varchar2(15), email varchar2(15), zipcode varchar2(15));
Output: Table created. SQL> desc Employee; Output:
Name Null? Type ----------------------------------------- -------- ----------------------------------------------SOCIALSECURITYNUMBER NOT NULL NUMBER(5) FNAME VARCHAR2(15) LNAME VARCHAR2 (15) ADDRESLINE1 VARCHAR2 (50) ADDRESLINE2 VARCHAR2 (15) CITY VARCHAR2 (15) STATE VARCHAR2 (3) PHONE VARCHAR2 (15) ZIPCODE VARCHAR2 (15) EMAILID VARCHAR2 (15) Valid Test Data b) SQL> insert into Employee values(&Social Security
Number,'&Fname,’&LName’ ,’&AddressLine1’ ,’&AddressLine2 ‘, ‘&city’ ,’&state
‘,’&phone’ ,’&zipcode’ ,’&email’);
SQL> select * from Employee;
Output:
SOCIALSECURITYNUMBER FNAME LName ,AddressLine1 AddressLine2 city state
phone zipcode email 100 ramu SS DDD 2345
101 kamal MONIKA MONIKA SSS 343434 34567 OHIO 343434 12000 12000 OHIODDD@GMAIL.COM
SSS OHIODDD@GMAIL.COM SQL> create table customer (Customerid number(5) primary key, Emp_FName varchar2(15), Emp_LName
varchar2(15), AddressLine1 varchar2(50), AddressLine2 varchar2(15), city varchar2(15),state varchar2(3),
phone varchar2(15), email varchar2(15), zipcode varchar2(15),DateofBirth Date,Hourlywages
number(3),date _of_hire Date, status varchar2(1)); Output: Table created. SQL> desc customer; SQL>
Output: Table created. SQL> desc Employee; Output:
Name Null? Type ----------------------------------------- -------- ----------------------------------------------Customerid
FNAME
LNAME
ADDRESLINE1
ADDRESLINE2 NOT NULL NUMBER(5)
VARCHAR2(15)
VARCHAR2 (15)
VARCHAR2 (50)
VARCHAR2 (15) CITY VARCHAR2 (15) STATE VARCHAR2 (3) PHONE VARCHAR2 (15) ZIPCODE VARCHAR2 (15) DATEOFBIRTH Date EMAILID
HOURLYWAGES VARCHAR2 (15)
NUMBER(3) DATE_OF_HIRE Date STATUS VARCHAR2(1) SQL> create table total(Employee_SSNnumber(4) primary key,
total_name varchar2(15),price number(6,2));
SQL> dsec total Output:
Name Null? Type ……………………………………………………………………………………………………
SocialSecurityNumber NOT NULL Total_name VARCHAR2(15) PRICE NUMBER(6,2) SQL>insert into total values(&total_id,’&total_name’,&price); SQL> select * from total;
Output:
TOTAL_ID TOTAL_NAME PRICE ……………………………………………………………………………………..
2334 geera 6.25 4532 corn soup 34.65 NUMBER(4) 2124 lays chips 4531 setwet 2319 duracell 20
99.99
45.5 SaleOrder_ID
Date_Of_Sale
Social Security Number
Employee_SSN
Total
Taxes SQL>create table sale(SalesOrder_IDnumber(5) primary key,Date_Of_Sales date,
SocialSecurityNumbernumber(5) references customer(Social Security Number),
Employee_SSNnumber(4) references total, Taxes number(4)); Out put: Table Created. SQL>dsec sale Output:
Name Null? Type ………………………………………………………………………………………..
SALESORDER_ID NOT NULL NUMBER(4) DATE_OF_SALES DATE SOCIAL SECURITY NUMBER NUMBER(5) TOTAL_ID
TAXES NUMBER(4)
NUMBER(4) SQL>insert into Sale values(&bill_no, ’&Date_Of_Sales’,
&Social Security Number, &total &Taxes); SQL>select * from sale;
Output:
SALESORDER_ID DATE_OF_SALES SOCIALSECURITYNUMBER TOTAL TAXES ………………………………………………………………………………………………………...
1450 02-JAN-2017 100 2124 2 1451 02-JAN-2017 101 2319 1 1452 02-JAN-2017 103 4531 2 1453 02-JAN-2017 102 2334 3 1454 02-JAN-2017 104 4532 3 c) List all the bills for the current date with the customer names and total numbers
SQL> select c.custname, i.total, s. SALESORDER from customer c, total I, sale s
where c.Social Security Number=s.SocialSecurityNumberand
s.date_of_sale=to_char(sysdate); CUSTNAME TOTAL SALESORDER ------------- --------- --------- John 5001 332 SQL> select SocialSecurityNumber, counts(SALESORDER_ID) from sale group by Social Security Number; SOCIAL SECURITY NUMBER
---------- --------------------1 2 3 1 4 1 5 1 COUNT(sales) Entities and Attributes
Supplier SupplierID SupplyType Contact City Email Product ProdID ProdName ProdTypeID BookID Price SupplierID ProdType ProdTypeID Description Order OrderID ProdID CustID Qty OrderDate TransID Customer CustID CustName Contact Book ISBN Title PublisherID Publisher PublisherID PublisherName Details Author AuthorID FirstName LastName Address BookAuthors ISBN AuthorID Rank Payment TransID TransType Amount Date CashierID Cashier CashierID CashierName Contact ER Diagram

• 1493951707-Computer Help 1 1.docx

ComputerScienceExpert

(11)

Status NEW Posted 05 May 2017 02:05 AM My Price 11.00

-----------

Attachments

file 1493951709-Solutions file 2.docx preview (51 words )

H-----------ell-----------o S-----------ir/-----------Mad-----------am ----------- Th-----------ank----------- yo-----------u f-----------or -----------you-----------r i-----------nte-----------res-----------t a-----------nd -----------buy-----------ing----------- my----------- po-----------ste-----------d s-----------olu-----------tio-----------n. -----------Ple-----------ase----------- pi-----------ng -----------me -----------on -----------cha-----------t I----------- am----------- on-----------lin-----------e o-----------r i-----------nbo-----------x m-----------e a----------- me-----------ssa-----------ge -----------I w-----------ill----------- be----------- qu-----------ick-----------ly -----------onl-----------ine----------- an-----------d g-----------ive----------- yo-----------u e-----------xac-----------t f-----------ile----------- an-----------d t-----------he -----------sam-----------e f-----------ile----------- is----------- al-----------so -----------sen-----------t t-----------o y-----------our----------- em-----------ail----------- th-----------at -----------is -----------reg-----------ist-----------ere-----------d o-----------n -----------THI-----------S W-----------EBS-----------ITE-----------. ----------- Th-----------ank----------- yo-----------u -----------

Not Rated(0)

• Buy Answer