1. Design a set of database tables to store people and cars. A person has a name

Question

1. Design a set of database tables to store people and cars. A person has a name, a unique driver license number, and an address. Every car has a unique vehicle identification number, manufacturer, type, and year. Every car has one owner, but one person can own multiple cars. Show the CREATE TABLE statements that could be used to create these two tables.

2. Give SQL commands to create a Book table, with columns for the ISBN, author, and title, and to insert all textbooks that you are using this semester.

3. Using the tables for the invoice database used as an example in chapter 23, give a SQL query that lists all customers who have an unpaid invoice (the amount paid on the invoice is zero).

Explanation / Answer

1)

Creating the Person Table:

CREATE TABLE PERSON

(

DRIVER_LICENSE_NUMBER INTEGER PRIMARY KEY,

NAME CHAR (20),

ADDRESS CHAR (50)

)

Creating the Car Table:

CREATE TABLE CAR

(

VEHICLE_IDENTIFICATION_NUMBER INTEGER PRIMARY KEY,

MANUFACTURER CHAR (20),

TYPE CHAR (20),

YEAR INTEGER

)

Creating Multiple Car owner table:

CREATE TABLE MULTIPLECARS

(

NAME CHAR (20),

DRIVER_LISENCE_NUMBER INTEGER,

CAR_ID INTEGER

)

2)

Creating Book Table:

CREATE TABLE BOOK

(

BOOK_ISBN CHAR (30),

AUTHOR CHAR (30),

TITLE CHAR (30)

)

Inserting the text books into the book table:

INSERT INTO BOOK VALUES (‘123456-10-12’, ‘CAY HORSSTMANN’, ‘BIG JAVA’);

Similarly any number of records can be inserted into table.

3)

Following is the SQL query that lists all customers who have an unpaid invoice:

SELECT CUSTOMER_NAME FROM CUSTOMER, INVOICE

WHERE

(CUSTOMER.CUSTOMER_NAME = INVOICE.CUSTOMER_NAME)

AND

(INVOICE.PAYMENY>0)

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

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