SQL Assignment Set up your own database, and Set up the tables you think are app

Question

SQL Assignment Set up your own database, and Set up the tables you think are appropriate for an organization that takes orders and sells or distributes something. (Or something else of comparable complexity.) It could be a store selling fruits and vegetables, a food bank, a business selling computer parts, pottery, space ships, towels, or digital watches; it could even be a group distributing CD's of open-source software In your own database, set up at least two tables, using CREATE TABLE (and ALTER TABLE as needed) for a one-many relationship, such as people to their phone numbers (nowadays, many people have a set of more that one phone number. Use a short key such as a number or initials, or login name, to make the connection Declare referential integrity (REFERENCES) in the phone number table, declare a primary key in the people table . . Insert some data. I will query your database, to find out about 'Lin Jensen'. To complete the lab, you should put in some made up information about me. (Assuming your table features people.) Also, use a table to implement a many:many relationship. A relationship "contacts" giving, for each person, the set of people they desire to contact (phone or send email to, for instance) would be reflexive, that is, from people to people. It would need 2 columns, one for the sender of the emails, the other for the receiver. Similarly, a genealogy could relate parents to children

Explanation / Answer

Steps

1. Create database named 'MyStore'

2. Create tables

3. Insert data to the tables

4. Test database using sql select statements.

Tables Required

Customer

ContactDetails - One to Many with Customer

Product

Orders

Sales

ContactPeople - Many to many with Customer

SQL Statements to create tables

CREATE TABLE CUSTOMER(CustomerID INT NOT NULL,FName VARCHAR(25),LName VARCHAR(25),Address VARCHAR(150),EmailID VARCHAR(75),PRIMARY KEY(CustomerID));

CREATE TABLE ContactDetails(CustomerID INT NOT NULL, Phone VARCHAR(20) NOT NULL,FOREIGN KEY(CustomerID) REFERENCES CUSTOMER(CustomerID) );

CREATE TABLE Product(PID INT NOT NULL,PName VARCHAR(25) NOT NULL,Description VARCHAR(50),PRIMARY KEY(PID));

CREATE TABLE Orders(OrderID INT NOT NULL, PID INT NOT NULL,CustomerID INT NOT NULL,Orderdate DATE,PRIMARY KEY(OrderID),FOREIGN KEY(PID) REFERENCES Product(PID));

CREATE TABLE ContactPeople(CustomerID INT NOT NULL,ContactPhone VARCHAR(20) NOT NULL,FOREIGN KEY(CustomerID) REFERENCES CUSTOMER(CustomerID));

CREATE TABLE Sales(OrderID INT NOT NULL,Amount FLOAT NOT NULL,invoicedate DATE,PRIMARY KEY(OrderID),FOREIGN KEY(OrderID) REFERENCES Orders(OrderID));

SQL Statements to insert sample data to tables

INSERT INTO CUSTOMER VALUES(1,'Samuel','George','Street1','smat@com');
INSERT INTO CUSTOMER VALUES(2,'William','George','Street2','wl@com');
INSERT INTO ContactDetails Values(1,'0093456888');
INSERT INTO ContactDetails Values(1,'0093456899');
INSERT INTO Product VALUES(1,'cadbury dairy milk','Dark Chocolate');
INSERT INTO Orders VALUES(1,1,1,'17-10-2017');
INSERT INTO ContactPeople VALUES(1,'23342200');
INSERT INTO ContactPeople VALUES(1,'23342222');
INSERT INTO ContactPeople VALUES(2,'23342200');

SQL Statements to view table data
select * from CUSTOMER;
select * from ContactDetails;
select * from Product;
select * from Orders;
select * from ContactPeople;

Name Type Key/Description CustomerID INT PK FName VARCHAR LName VARCHAR Address VARCHAR EmailID VARCHAR

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.