TOPIC : CAR RENTAL I HAVE A PROVIDED A LOT OF INFORMATION YOU MIGHT NOT NEED TO

Question

TOPIC :

CAR RENTAL

I HAVE A PROVIDED A LOT OF INFORMATION YOU MIGHT NOT NEED TO USE IT. I WILL PUT THE INFORMATION IN                   “***”

1)**************create all the tables using the information provides.(Display all the table in table format with the data type and data description ,length ,default value )****

Example

Table Name:          

Attribute

Description

Key Type/

Constraint

Data Type

Filed length

2***********Create and execute all the SQL statements needed to add the constraints***********

3***********Create and alter the tables based on the needs ( min 5 tables maximum is 10)**************

YOU CAN USE UNION INTERSECTION AND UNION ALL OR ANY OTHER METHOD FOR MAKING THE 5 TO 10 QUERIES.

ALSO, THIS IS A MYSQL DATABASE TABLE. IHAVE MADE THE VISIO FILE FOR DESCRIBING THE DATABASE.

GIVEN BELOW ARE THE STYLES OF QUERIES WHICH U CAN USE IF U WANT TO NO COMPULSION..

-- cartesian join

select firstname,customer#,ISBN,title from customers,books;

select c.firstname,customer#,b.ISBN,b.title from customers c,books b;

select title,name from books join publisher using (pubid);

-- self join using on clause

select r.lastname,r.firstname ,c.lastname " Referred By "   from

customers c join customers r

on c.REFERRED=r.CUSTOMER#;

--INNER JOIN

select title,pubid from publisher INNER join books USING (PUBID) ORDER BY PUBID;

-- RIGHT

SELECT title,pubid from publisheR RIGHT JOIN books USING (PUBID) ORDER BY PUBID;

--LEFT JOIN

SELECT TITLE,PUBID FROM BOOKS LEFT JOIN PUBLISHER USING (PUBID);

-- FULL JOIN

SELECT TITLE,PUBID FROM BOOKS FULL JOIN PUBLISHER USING (PUBID) ORDER BY 2 ;

-- Equi join LEFT(+)

SELECT C.LASTNAME ,C.FIRSTNAME ,O.ORDER# FROM CUSTOMERS C,ORDERS O

WHERE C.CUSTOMER#=O.CUSTOMER#(+);

- HAVING CLAUSE WITH IN OPERATOR

SELECT CATEGORY,MIN(RETAIL) FROM BOOKS where

CATEGORY IN ('COMPUTER','LITERATURE')

group by category HAVING MIN(RETAIL)>25;

-- rewrite the above query using where clause

select r.lastname,r.firstname ,c.lastname " Referred By "   from

customers c , customers r where c.REFERRED=r.CUSTOMER#;

-- Traditional method vs join

select r.lastname,r.firstname ,c.lastname " Referred By "   from

customers c , customers r where c.REFERRED=r.CUSTOMER# ;

select r.lastname,r.firstname ,c.lastname " Referred By "   from

customers c join customers r on c.REFERRED=r.CUSTOMER#;

Attribute

Description

Key Type/

Constraint

Data Type

Filed length

first name phane phene no Customer Employee t-Mail salesiD eustemeriD Sales Info cerID Price employeelD Car make Payment year prymentvMethod

Explanation / Answer

Answer)

1)SQL statements to create all the tables using the information provided:

Creating table Customer:

CREATE TABLE Customer(

CustomerID int primary key,

firstname varchar,

lastname varchar,

PhoneNumber varchar,

Address varchar

);

Creating Table Employee:

CREATE TABLE Employee(

EmployeeID int primary key,

Name varchar,

Phone varchar,

Email varchar

);

Creating table SalesInformation:

CREATE TABLE SalesInformation(

SalesID int primary key,

CustomerID int,

EmployeeID int,

TrasactionNumber int,

CardID int,

FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID),

FOREIGN KEY (EmployeeID) REFERENCES Employee(EmployeeID),

FOREIGN KEY (TrasactionNumber) REFERENCES Payment(TrasactionNumber),

FOREIGN KEY (CardID) REFERENCES Car(CardID)

);

Creating table Payment:

CREATE TABLE Payment(

TrasactionNumber int primary key,

CustomerID int,

SalesID int,

PaymentID int

);

Creating table Car:

create table Car(

CarID int primary key,

Make varchar,

Model varchar,

Year varchar,

Price varchar

);

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

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