Question

The first several questions use the following tables, which model a ticketing system (the venue table is mentioned but not shown). holderId eventId holderId name eventId name venue date 10 20 30 John Ella Miles Lilo ComicCon Jazzfest MakerFaire 300 100 200 2019-07-18 2019-04-06 2019-05-17 10 ticket number 10000 10001 200 210 101 100 20 1. The above relations are missing primary and foreign key constraints. Write out the intended keys and foreign keys. You may use English and relational algebra, or the table creation syntax. 2. Which columns (or groups of columns) of the above relations should be NOT NULL or UNIQUE? 3. What will happen if we run the following query (Write out the new contents of the relations) DELETE FROM holder WHERE holderId = 1: (a) if all of the reference policies are the default policy? (b) if all of the reference policies are CASCADE SET NULL? (c) if all of the reference policies are CASCADE DELETE? 4. Write the results of the following queries: (a) SELECT 1 AND NULL; (b) SELECT O AND NULL; (c) SELECT O OR NULL; (d) SELECT 1 OR NULL; (e) SELECT NULL = NULL; (f) SELECT NULL. IS NULL;

Tables names are not given, According to the 1st Question we have to name them from the given Key names, Like this;

Answer 1

Here is whole Database

MariaDB [hh]> desc holder; + - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - + - - - - - + - - - - - - - + - - - - - - - + | Field Type Null | Key | Default | Extra || +---------+---- ---------+------+-----+---------+-------+ holderid | int(2) name varchar(50) NO YEST PRI NULL | NULL MariaDB [hh]> select * from holder; +----------+-------+ | holderid | name | +----------+-------+ 1 | John 2 | Ella 3 Miles 4 | Lilo +------ 4 rows in set (0.00 sec) | T + - - - - - - - - - - + - - - - - - - - - - - - + - - - - + - - - + - - - + - - - - - - - + 2 rows in set (0.01 sec) - - + - - - -- - - + MariaDB [hh]> desc event; - - + - - - - - - - - - - - - - + - - - - - - + - - -- - - - - - - + MariaDB [hh1> select * from event: | Field Type Null | Key Default | Extra || -- - - - - + - - - - - - - - - - - - + - - - - - - - + -- + - - - - - + - - - - - - + - - - - - - - +- - - - - - - - - - - - + | eventid | int(3) name varchar(50) | venue int(5) | date date NO PRI YEST | YEST | YEST NULL NULL NULL NULL eventid | name venue date +---------+---- 10 | ComicCon L 300 2019-07-18 20 Jazzfest L 100 2019-04-06 30 MakerFaire | 200 | 2019-05-17 + - - - - - - - + - - - - - - - + - - - - -- + - - - - - - - - + - - - - - - - - - - - + - - - - - + - - - - - + - - - - - - - - - + - - - - - - - + 3 rows in set (0.00 sec) 4 rows in set (0.00 sec) MariaDB [hh]> select * from ticket; MariaDB [hh]> desc ticket; +----------+--------- --------------+ + - = - - = - = - = - = - = - = - = - = - = - = - = - = - - = = = = = = = = = = = = = = = holderid | eventid | ticketnumber 1 | Field Type Null | Key Default | Extra | + - - - - - - - - - -+ - - - - - - - - -+ - - - - - - + -------------- -------- ------ ----- ---------+-------+ | | holderid | int(2) | NO | eventid | int(3) | NO | ticketnumber | int(8) NOT MUL | NULL MUL NULL | NULL 10 30 10 20 30 30 10000 10001 200 210 101 100 + - - - - - = = = = = = + = = = = = = = = + - - - - - - + - - - - + - - - - - - - 3 rows in set (0.00 sec) 41 + - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - + 6 rows in set (0.12 sec) MariaDB [hh]> desc venue; + - - - - - - - - - - + - - - - - - - - - - - - - + - - - - - - - MariaDB [hh]> select * from venue; | Field Type Null | Key | Default | Extra - - - - - - -- + + - - - - - - - + - - - - - - - - - - - - - - - - - + - - - - - + - - - - - - - - - + - - - - - - - + venueid holderid | eventid venuename | + - - - - - - - - -+ - - - - - - - - - -+ - - - - - - - - - + - - - - - - - - - - -+ | venueid holderid eventid venuename | int(3) NO int(2) NO int(3) NO varchar(50) | NOT PRI MUL MUL 101 NULL NULL NULL NULL 102 103 21 31 10 USA 10 | UK 30 | NZ + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - + - - - - - - - - - + - - - - - - - + + - - - - - - - - - + - - - - - - -- -+ - - -- - - -- - -- - - 4 rows in set (0.00 sec) 3 rows in set (0.00 sec)

ANSWERING YOUR QUESTION

1.

Adding Primary Keys

 ALTER TABLE `event` ADD PRIMARY KEY (`eventid`); ALTER TABLE `holder` ADD PRIMARY KEY (`holderid`);

Adding Foreign Key

 ALTER TABLE `ticket` ADD KEY `holderid` (`holderid`), ADD KEY `eventid` (`eventid`);

Making Venue Table

 CREATE TABLE `venue` ( `venueid` int(3) NOT NULL, `holderid` int(2) NOT NULL, `eventid` int(3) NOT NULL, `venuename` varchar(50) NOT NULL ); ALTER TABLE `venue` ADD PRIMARY KEY (`venueid`), ADD KEY `eventid` (`eventid`), ADD KEY `holderid` (`holderid`);

2.

HolderID from Holder NOT NULL , UNIQUE

EventID from Event NOT NULL , UNIQUE

VenueID from NOT NULL , UNIQUE

TicketNumber from Ticket NOT NULL

3.

(a) It will produce error if set as default policy

Cannot delete or update a parent row: a foreign key constraint fails