Expert answer:Week 4 Project
Expert answer:Part I—Hands-on Lab For the hands-on lab, you will submit a Microsoft Access database and a Microsoft Word document with SQL statements for the following: Create a new Microsoft Access database named Travel. Note that Microsoft Access does not support the use of SQL scripts to create databases. In Microsoft Word, type the SQL script that you would have used to create the database. Now, add a table named Car_Rental to the Travel database. Make sure that you include the Car_Rental_ID field whose datatype is AutoNumber. In Microsoft Word, type the SQL script that you would have used to create the table. Include the following fields: Car_Rental_ID (AutoNumber, primary key, required) Agency (Text(25), required) Size (Text(25), required) Make (Text(12), required) Model (Text(25)) Daily_Cost (Currency) Write INSERT statements for adding the following data to the new table: Agency Size Make Model Daily_Cost Avis Mid Ford 89.95 Avis Full Ford Taurus 112.95 Hertz Comp Chev Cavalier 69.95 Hertz Mid Chev Malibu 84.99 Hertz Full Chev Impala 114.99 National Mid Chrys Stratus 77.95 National Full Chrys Intrepid 109.95 Verify the values inserted by using a query. Display each distinct agency (each agency only once). Display the average cost of a Chrysler rental. Display the total count of cars available. Display the highest-priced vehicle. Display each unique agency with the average car price for that agency. Part II—Course Project Course Project Part 4—Creating a Database Continuing with the project you worked on for Weeks 1, 2 and 3, this week, complete the following tasks: In Microsoft Access, create the database and tables that you identified in W3 Assignment 2. In Microsoft Word, write the SQL statements to create the database and tables. Write SQL statements to populate the tables. Run the SQL statements in Microsoft Access for populating the tables. Assignment Deliverable After you complete the lab and course project, collate the information from both in a 3- to 4-page report in a Microsoft Word document. Include the SQL scripts in the Microsoft Word document. Include the Travel database as a separate attachment.
su_its3102_w3_a2_still_j.docx
su_its3102_w3_a2_still_j.xlsx
Unformatted Attachment Preview
Running Head: W3A2 – Joshua Still
1
Joshua Still
SQL Development
South University Online
W3A2 – Joshua Still
PART 1: Hands-on Lab
SQL statements:
•
To update the publisher name from READ WITH US to READ FOR US
UPDATE Publisher SET [PubName] = ‘READ FOR US’
WHERE [PubName] = ‘READ WITH US’
•
To verify the updated name field for the publisher with ID 6
SELECT*FROM Publisher WHERE PubID = 6
•
To make the following updates to the Publisher table (be careful with WHERE):
o Make Contact=”John Travolta” for publisher with ID number 4
UPDATE Publisher SET [Contact] = ‘John Travolta’ WHERE [PubID] = 4
o
Make Phone=888-999-7777 for TEXT AND MORE
UPDATE Publisher SET [Phone] = ‘888-999-7777’ WHERE [PubName] = ‘TEXT AND
MORE’
•
To list the customer numbers and names of all individuals who have purchased books in
the information systems category
SELECT c.CustomerID, c.FirstName AS [First Name] , c.LastName AS [Last Name]
FROM Customers c,Orders o,Books b
WHERE c.CustomerID = o.CustomerID AND o.ISBN = b.ISBN AND b.Category =
‘Information Systems’
•
To identify the book written by an author with the last name Cain. Perform the search by
using the author name
SELECT* FROM BOOKS
WHERE [Author] lIKE ‘*Cain*’;
•
To produce a list of all the customers who live in the state of Indiana and have ordered
books on information systems
SELECT c.CustomerID, c.FirstName AS [First Name] , c.LastName AS [Last Name]
FROM Customers c,Orders o,Books b
2
W3A2 – Joshua Still
3
WHERE c.CustomerID = o.CustomerID AND o.ISBN = b.ISBN AND b.Category =
‘Information Systems’ AND c.State=’IN’
•
To display a list of all the books in the Books table. If a customer has ordered a book, list
the corresponding order number and the state in which the customer resides
SELECT title, o.OrderID, c.state FROM Books b ,Orders o,Customers c
WHERE b.ISBN = o.ISBN
AND
o.CustomerID=c.CustomerID
ORDER BY title
•
To identify the authors of the books ordered by Sheila Smith. Perform the search by using
the customer name
SELECT b.Author AS Authors
FROM Books b, Orders o, Customers c
WHERE b.ISBN = o.ISBN
AND o.CustomerID = c.CustomerID
AND c.FirstName = ‘Sheila’
AND c.LastName = ‘Smith’
•
To list the customer numbers and names of all the individuals who have purchased books
in the children category
SELECT c.CustomerID, c.FirstName AS [First Name] , c.LastName AS [Last Name]
FROM Customers c,Orders o,Books b
WHERE c.CustomerID = o.CustomerID
AND o.ISBN = b.ISBN
AND b.Category = ‘childrens’
•
To add the following items to the Books table:
ISBN
Title
1234567890 A Good Book
2314569874
Trains for
Everyone
1598745682 Eating In
Author
John
Jeffries
Corey
Berkey
Susan
Smythe
Another Vampire
Ann Oats
Story
Databases are
Tony
1025874159
Fun!
Stevens
99954123654
PubDate
Cost Retail Category
PubID
10/11/2005 13.5 15.00 Non-Fiction
7
12/15/2011 10.25 25.25 Children
8
09/09/2012 20.00 30.50 Cooking
6
09/05/2010 11.23 13.52 Fiction
4
01/01/2013 4.12 5.23
Information
Systems
9
W3A2 – Joshua Still
INSERT INTO Books VALUES (‘1234567890′,’A Good Book’,’John
Jeffries’,’10/11/2005′,13.5,15.00,’Non-Fiction’,7)
INSERT INTO Books VALUES (‘2314569874′,’Trains for Everyone’,’Cory
Berkey’,’12/15/2011′,10.25,25.25,’Children’,8)
INSERT INTO Books VALUES (‘1598745682′,’Eating In Another’,’Susan
Smythe’,’09/09/2012′,20.00,30.50,’Cooking’,6)
INSERT INTO Books VALUES (‘99954123654′,’Vampire Story’,’Ann
Oats’,’09/05/2010′,11.23,13.52,’Fiction’,4)
INSERT INTO Books VALUES (‘1025874159′,’Databases are Fun!’,’Tony
Stevens’,’01/01/2013′,14.12,5.23,’Information Systems’,9)
Confirm results
SELECT*FROM BOOKS
•
To delete the last record added
DELETE FROM BOOKS WHERE PubID = 9
List of books after deleting the last record added in the books table
SELECT*FROM BOOKS
•
To update the name Susan Smythe to Susan Smythe-Brown
UPDATE BOOKS SET [Author] = ‘Susan Smythe-Brown’ WHERE [Author] = ‘Susan
Smythe’
Result after updating the books table
SELECT*FROM Books
4
W3A2 – Joshua Still
ER Model:
5
W3A2 – Joshua Still
6
References:
Kroenke, D. M. (2016). Database Processing: Fundamentals, Design, and Implementation, 14th
Edition. [South University]. Retrieved
from https://digitalbookshelf.southuniversity.edu/#/books/9781323409466/
Passanger Table
Primary Key
Name of Column
Null(NorY)
Datatype
PK
Passenger_ID
N
Y
N
AutoNumber
Short Text
Short Text
Passenger_name
Address
Flight Table
Primary Key
PK
Name of Column
Flight_ID
Flight_name
Flight_time
Plain_ID
Null(NorY)
Datatype
N
Y
N
N
AutoNumber
Short Text
Date/Time
Number
Captain Table
Primary Key
PK
Name of Column
Captain_ID
Captain_name
Plain_ID
Null(NorY)
Datatype
N
Y
N
AutoNumber
Short Text
Number
Passanger Table
Description
Foreign Key
Foreign Key Table
Foreign Key
Foreign Key Table
Holds passanger’s unique number
Holds passanger’s name(s)
Holds passangers Address
Flight Table
Description
Holds the unique number for each flight
Holds the fright name
Holds the flight time of each flight
Holds ID number of each plain making a flight
FK
Flight Table
Captain Table
Description
Holds each captain unique number
Holds the name(s) of each captain
Holds ID number of each plain for each captain
Foreign Key
FK
Foreign Key Table
Flight Table
Booking Table
Primary Key
PK,FK
PK,FK
Name of Column
Passenger_ID
Flight_ID
Ticket_Number
Null(NorY)
Datatype
N
N
N
Number
Number
Short Text
Plane Table
Primary Key
PK
Name of Column
Plane_ID
Docking_bay
Null(NorY)
N
Datatype
AutoNumber
N
Short Text
Contact Table
Primary Key
Name of Column
Null(NorY)
Datatype
PK
Contact_ID
Passanger_ID
Contact_number
Email_address
N
N
Y
Y
AutoNumber
Number
Short Text
Short Text
Booking Table
Description
Holds the unique number for each Booking
Holds the flight number for each booking
Holds the ticket number for each booking
Foreign Key
FK
FK
Foreign Key Table
Passanger Table
Flight Table
Plane Table
Description
Holds the unique identifier for each plane
Foreign Key
Foreign Key Table
Foreign Key
Foreign Key Table
Holds the docking bay details for each plane
Contact Table
Description
Holds passanger’s contact unique number
Holds passanger’s number
Holds passager’s phone number
Holds passager’s email address
FK
Passangers Table
…
Purchase answer to see full
attachment
How it works
- Paste your instructions in the instructions box. You can also attach an instructions file
- Select the writer category, deadline, education level and review the instructions
- Make a payment for the order to be assignment to a writer
- Download the paper after the writer uploads it
Will the writer plagiarize my essay?
You will get a plagiarism-free paper and you can get an originality report upon request.
Is this service safe?
All the personal information is confidential and we have 100% safe payment methods. We also guarantee good grades