Fundamentals of Database Management Systems:

Test 1
Implement the following tables form part of a database held in a relational DBMS:

1.TblHotel
Column Domain Constraint
hotelNo Int Pk, not null, A_I
hotelName Varchar(30) Not null, unique
city Varchar(15) Not null

To create the "tblHotel" table in a relational database management system (DBMS), you can use SQL
statements. Here's an example of how you can implement the table with the specified columns,
domains, and constraints:

My SQl code
CREATE TABLE tblHotel (hotelNo INT PRIMARY KEY AUTO_INCREMENT, hotelName VARCHAR(30) NOT
NULL UNIQUE,city VARCHAR(15) NOT NULL);

In the above SQL statement:

 The `CREATE TABLE`command is used to create a new table named "tblHotel."

 The column `hotelNo` is defined as an INT data type with the `PRIMARY KEY` constraint
and `AUTO_INCREMENT` attribute, which automatically generates a unique value for each
row.
 The column `hotelName` is defined as a `VARCHAR(30)` data type, specifying that it can
store up to 30 characters. It has the NOT NULL constraint, ensuring that the column must
have a value for each row. Additionally, the `UNIQUE` constraint ensures that each hotel
name must be unique in the table.
 The column city is defined as a `VARCHAR(15)` data type, allowing a maximum of 15
characters. It also has the `NOT NULL` constraint to enforce non-null values for each row.

By executing the above SQL statement, you can create the "tblHotel" table in your relational
DBMS.

2. TblRoom
Column Domain Constraint
roomno Int PK, not null,
hotleno Int PK, not null,
type Enum(1bed,2bed,3bed)
 price float
My SQl code

CREATE TABLE tblRoom (roomNo INT NOT NULL, hotelNo INT NOT NULL, type ENUM('1bed', '2bed',
'3bed') NOT NULL,price FLOAT NOT NULL DEFAULT 1000,PRIMARY KEY (roomNo, hotelNo));

In the above SQL statement:

 The `CREATE TABLE` command is used to create a new table named "tblRoom."
 The column `roomNo` is defined as an `INT` data type and is marked as `NOT NULL`since
it is part of the primary key.
 The column `hotelNo` is defined as an `INT` data type and is also marked as `NOT
NULL` since it is part of the primary key.
 The column type is defined as an `ENUM` data type with three possible values: '1bed',
'2bed', and '3bed'. It is marked as `NOT NULL` to ensure that every row has a valid room
type.
 The column price is defined as a `FLOAT` data type and is marked as `NOT NULL`. It also
has a default value of 1000, which will be used if no specific price is provided.
 The primary key constraint is specified using the `PRIMARY KEY` keyword, indicating
that the combination of `roomNo` and `hotelNo` uniquely identifies each row in the
table.

By executing the above SQL statement in your relational DBMS, you can create the "tblRoom"
table with the specified columns, domains, and constraints.

3.TblBooking
Column Domain Constraint
hotelNo Int PK
guestNo Int PK
datefrom Date Default;a week from
today
dateto Date
roomno Int

My SQl code

CREATE TABLE tblBooking (hotelNo INT NOT NULL, guestNo INT NOT NULL,dateFrom DATE DEFAULT
CURDATE() + INTERVAL 7 DAY,dateTo DATE,roomNo INT,PRIMARY KEY (hotelNo, guestNo),FOREIGN
KEY (hotelNo) REFERENCES tblHotel(hotelNo),FOREIGN KEY (guestNo) REFERENCES tblGuest(guestNo),
FOREIGN KEY (roomNo) REFERENCES tblRoom(roomNo));

In the above SQL statement:

 The CREATE TABLE command is used to create a new table named "tblBooking."
 The column hotelNo is defined as an INT data type and is marked as NOT NULL since it is
part of the primary key.
 The column guestNo is defined as an INT data type and is also marked as NOT NULL since
it is part of the primary key.
 The column dateFrom is defined as a DATE data type and has a default value set to a week
from today. The CURDATE() function retrieves the current date, and INTERVAL 7 DAY
adds seven days to it.
 The column dateTo is defined as a DATE data type and does not have a default value. It
will store the end date of the booking period.
 The column roomNo is defined as an INT data type and will store the room number
associated with the booking.
 The primary key constraint is specified using the PRIMARY KEY keyword, indicating that
the combination of hotelNo and guestNo uniquely identifies each booking.
 Three foreign key constraints are specified using the FOREIGN KEY keyword and
REFERENCES clause. These constraints establish referential integrity with the respective
referenced tables (tblHotel, tblGuest, and tblRoom).

By executing the above SQL statement in your relational DBMS, you can create the
"tblBooking" table with the specified columns, domains, and constraints.

4.TblGuest
Column Domain Constraint
Guest No Int pK, a_i, Not null
Guest name Varchar(40) Not null
Guest address Varchar(40)
Guest email Varchar(40)
Guest phone Varchar(12)

My SQl code

CREATE TABLE tblGuest ( guestNo INT PRIMARY KEY AUTO_INCREMENT, guestName VARCHAR(40)
NOT NULL,guestAddress VARCHAR(40),guestEmail VARCHAR(40),guestPhone VARCHAR(12));

In this example, we use the `CREATE TABLE` statement to create a table named "tblGuest." The
columns and their corresponding domains (data types) are specified, along with the constraints.
  `guestNo` is defined as an `INT` (integer) column. The `PRIMARY KEY` constraint
designates it as the primary key of the table. The `AUTO_INCREMENT` attribute allows the
system to automatically generate a unique value for this column when a new row is
inserted. The NOT NULL constraint ensures that the value cannot be empty.
 `guestName` is defined as a `VARCHAR(40)` column, which can hold a variable-length
string of up to 40 characters. The `NOT NULL` constraint ensures that a value is always
required.
 `guestAddress`, `guestEmail`, and `guestPhone` are also defined as `VARCHAR(40)`
columns, allowing up to 40 characters. These columns are not specified as `NOT NULL`,
so they can contain null values (i.e., empty or missing data).

Note that the specific syntax for creating tables may vary slightly depending on the DBMS you
are using. The example provided above assumes the usage of a DBMS that supports the standard
SQL syntax.