DROP TABLE IF EXISTS BOOKING;
DROP TABLE IF EXISTS [EVENT];
DROP TABLE IF EXISTS TOUR;
DROP TABLE IF EXISTS CLIENT;

GO

CREATE TABLE CLIENT(
    CLIENTID    INT,
    SURNAME     NVARCHAR(100) NOT NULL,
    GIVENNAME   NVARCHAR(100) NOT NULL,
    GENDER      NVARCHAR(1),
    CONSTRAINT PK_CLIENT PRIMARY KEY(CLIENTID),
    CONSTRAINT CHK_GENDER CHECK (GENDER IN('M','F','I'))
);

CREATE TABLE TOUR(
    TOURNAME        NVARCHAR(100),    
    [DESCRIPTION]   NVARCHAR(500),
    CONSTRAINT PK_TOUR PRIMARY KEY(TOURNAME)
);

CREATE TABLE EVENT(
    TOURNAME    NVARCHAR(100),
    EVENTMONTH  NVARCHAR(3),
    EVENTDAY    INT,
    EVENTYEAR   INT,
    EVENTFEE    MONEY NOT NULL,
    CONSTRAINT PK_EVENT PRIMARY KEY(TOURNAME, EVENTMONTH, EVENTDAY, EVENTYEAR),
    CONSTRAINT FK_EVENT_TOUR FOREIGN KEY(TOURNAME) REFERENCES TOUR,
    CONSTRAINT CHK_EVENT_EVENTMONTH CHECK(EVENTMONTH IN('Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec')),
    CONSTRAINT CHK_EVENT_EVENTDAY CHECK(EVENTDAY >= 1 AND EVENTDAY <= 31),
    CONSTRAINT CHK_EVENT_EVENTYEAR CHECK(LEN(EVENTYEAR) = 4),
    CONSTRAINT CHK_EVENTFEE CHECK(EVENTFEE > 0)
);

CREATE TABLE BOOKING(
    CLIENTID INT,
    TOURNAME    NVARCHAR(100),
    EVENTMONTH  NVARCHAR(3),
    EVENTDAY    INT,
    EVENTYEAR   INT,
    PAYMENT     MONEY,
    DATEBOOKED DATE NOT NULL,
    CONSTRAINT PK_BOOKING PRIMARY KEY(CLIENTID, TOURNAME, EVENTMONTH, EVENTDAY, EVENTYEAR),
    CONSTRAINT FK_BOOKING_EVENT FOREIGN KEY(TOURNAME, EVENTMONTH, EVENTDAY, EVENTYEAR) REFERENCES [EVENT],
    CONSTRAINT FK_BOOKING_CLIENT FOREIGN KEY(CLIENTID) REFERENCES CLIENT,
    CONSTRAINT CHK_BOOKING_EVENTMONTH CHECK(EVENTMONTH IN('Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec')),
    CONSTRAINT CHK_BOOKING_EVENTDAY CHECK(EVENTDAY >= 1 AND EVENTDAY <= 31),
    CONSTRAINT CHK_BOOKING_EVENTYEAR CHECK(LEN(EVENTYEAR) = 4),
    CONSTRAINT CHK_PAYMENT CHECK(PAYMENT> 0)
);
GO

INSERT INTO CLIENT(CLIENTID, SURNAME, GIVENNAME, GENDER)VALUES
(1, 'Price','Taylor','M'),
(2, 'Gamble','Ellyse','F'),
(3, 'Tan', 'Tilly', 'F'),
(4, 'Onna', 'Myowney', 'I');

INSERT INTO TOUR (TOURNAME, [DESCRIPTION]) VALUES
('North',   'Tour of wineries and outlets of the Bedigo and Castlemaine region'),
('South',   'Tour of wineries and outlets of Mornington Penisula'),
('West',    'Tour of wineries and outlets of the Geelong and Otways region');

INSERT INTO EVENT (TOURNAME, EVENTMONTH, EVENTDAY, EVENTYEAR, EVENTFEE) VALUES
('North', 'Jan', 9, 2022, 200),
('North', 'Feb', 13, 2022, 225),
('South', 'Jan', 9, 2022, 200),
('South', 'Jan', 16, 2022, 200),
('West', 'Jan', 29, 2022, 225),
('West', 'Feb', 12, 2022, 225);

INSERT INTO BOOKING (CLIENTID, TOURNAME, EVENTMONTH, EVENTDAY, EVENTYEAR, PAYMENT, DATEBOOKED) VALUES
(1, 'North', 'Jan', 9, 2022, 200, '2021/12/10'),
(2, 'North', 'Jan', 9, 2022, 200, '2022/01/03'),
(1, 'North', 'Feb', 13, 2022, 225, '2022/01/28'),
(2, 'North', 'Feb', 13, 2022, 125, '2022/01/14'),
(3, 'North', 'Feb', 13, 2022, 225, '2022/02/10'),
(1, 'South', 'Jan', 9, 2022, 200, '2021/12/10'),
(1, 'South', 'Jan', 16, 2022, 200, '2022/1/11'),
(2, 'South', 'Jan', 16, 2022, 200, '2021/12/18'),
(3, 'South', 'Jan', 16, 2022, 200, '2022/01/15'),
(2, 'West', 'Jan',  29, 2022, 225, '2022/01/17'),
(3, 'West', 'Jan',  29, 2022, 200, '2021/12/18');

using the information above answer the following SQL related questions, answer each question with a simple single sentence 15 words or less unless the question requires you to answer with sql code.

 Question 11

Provide a brief definition of the following term.

Table

Provide an example of a Table from the database used in this assessment.

Column

Provide an example of a Column from the database used in this assessment.

Row

Provide an example of a Column from the database used in this assessment.

 

Question 12

Briefly explain the difference between a Database and a DBMS.

 
Question 13


Provide a brief definition of the following terms:

Primary Key (PK):

Provide an example of a PK from the database used in this assessment.


Foreign Key (FK):

Provide an example of a FK from the database used in this assessment.

Constraint:
Provide an example of a (non PK or FK) constraint from the database used in this assessment.

 




Question 14


Provide a brief definition of the following terms, including the Command Keywords used in each (e.g. SELECT is part of DML)

Data Definition Language (DDL) 

Data Manipulation Language (DML)