Answered step by step
Verified Expert Solution
Question
1 Approved Answer
I also publish the same provided with the picture to make your work easier. i appreciate your work. Thank you very much SHORT SUMMARY: Provided
I also publish the same provided with the picture to make your work easier. i appreciate your work. Thank you very much
SHORT SUMMARY:
- Provided the queries as per the requirement.
- Given the add constraint statement just to check the docking fee od port table should be greater than 50.
SQL QUERY:
CREATE DATABASE jw_A3; USE jw_A3; CREATE TABLE SHIP( ShipID INT PRIMARY KEY, ShipName VARCHAR(20), [Weight] DECIMAL(18,2), YearBuilt INT, PassengerCapacity INT ); CREATE TABLE CRUISE( CruiseSerialNo INT, SailingDate DATE, ReturnDate DATE, ShipID INT, PRIMARY KEY(CruiseSerialNo), FOREIGN KEY(ShipID) REFERENCES SHIP(ShipID) ); CREATE TABLE PASSENGER( PassengerID INT PRIMARY KEY, PFirstName VARCHAR(20), PLastName VARCHAR(20), PStreetAddress VARCHAR(20), PCity VARCHAR(20), PState VARCHAR(20), PPostalCode INT, PCountry VARCHAR(20), ) CREATE TABLE BOOKING( CruiseSerialNo INT, PassengerID INT, Price DECIMAL(18,2), SatisfactionRate INT, PRIMARY KEY(CruiseSerialNo,PassengerID), FOREIGN KEY(CruiseSerialNo) REFERENCES CRUISE(CruiseSerialNo), FOREIGN KEY(PassengerID) REFERENCES PASSENGER(PassengerID) ); CREATE TABLE PORT( PortId INT PRIMARY KEY, PortName VARCHAR(20), PortCountry VARCHAR(20), DockingFee DECIMAL(18,2) ) CREATE TABLE VISIT( CruiseSerialNo INT, PortID INT, VisitOrder INT, PRIMARY KEY(CruiseSerialNo,PortID), FOREIGN KEY(CruiseSerialNo) REFERENCES CRUISE(CruiseSerialNo), FOREIGN KEY(PortID) REFERENCES PORT(PortID) ) ALTER TABLE PORT ADD CONSTRAINT CHK_DOCKINGFEE CHECK (DockingFee > 50);
**************************************************************************************
Create a database in SQLServer for the relational model provided in the HW3- Relational Schema file. Populate your database with some data to test your database. Write the requested queries in SQL. The relational model that is provided is part of the relational model from your HW 2, however it has been simplified. The E-R diagram for this relational model is also provided to you in the HW3_Data Model file SHIP Ship D ShipName Weight YearBuilt PassengerCapacity . CRUISE CruiseSerialNo SailingDate ReturnDate ShipD Booking CruiseSerialNo Passenger D Price Satisfaction Rate First create an SQL Server database by right clicking on the databases folder in the Object explorer and selecting the New Database option. Name the database by the initials of your last names and then A3. For instance a database created by Johnson and White would be named as jw_A3. Write the SQL statements for creating the tables in your database, specifying data types and field lengths, establishing primary keys and foreign keys, and implementing all the necessary constraints. Save your create statements in a file. Use the same naming convention you used to name your database to name your file. In your file also include the following SQL statements 1. An SQL statement to add a constraint to one of the tables. 2. An SQL statement to add a column to one of the tables. 3. An SQL statement to insert data to one of the tables 4. An SQL statement to modify the data in one of the tables. 5. An SQL statement to delete a row of data from one of the tables. Populate your database with sample data. Passenger PassengerID PFirstName PlastName PStreetAddress PCity PState PPostalCode PCountry VISIT Cruise SerialNo PortID VisitOrder PORT Portld | PortName PortCountry | DockingFee World Traveler Cruise Line laccidently didn't publish the rest. the given answer is below can you please reformat it and make changes if it is necessary. SHORT SUMMARY: CRUISE Cruise Serial Number Sailing Date Return Date VISITS Visit Order SHIP Ship Number Ship Name Weight Year Built Passenger Capacity Provided the queries as per the requirement. Given the add constraint statement just to check the docking fee od port table should be greater than 50. SQL QUERY: CREATE DATABASE jw_A3; USE jw_A3; BOOKING Price Satisfaction Rate PORT PortID Port Name Country Docking Fee CREATE TABLE SHIP ShipID INT PRIMARY KEY, ShipName VARCHAR(20). [Weight] DECIMAL(18,2). YearBuilt INT, PassengerCapacity INT CREATE TABLE CRUISE Cruise SerialNo INT, SailingDate DATE, ReturnDate DATE, ShipID INT. PRIMARY KEY(CruiseSerialNo), FOREIGN KEY(ShipID) REFERENCES SHIP(ShipID) PASSENGER Passenger Number Name (First Name, Middle Name, Last Name) Address (Street Address, City State, Postal Code, Country)Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started