Relational Database Design - Assignment Example

Relational Database Design The Design of a Relational Database by This paper describes the process of creating a relational database. Itincludes the SQL code (MySQL) for creating the tables, the joins to create the necessary relationships and the results of those SQL runs. It describes the specifications of the proposed system, the logic of that system, the table definitions and contents, a live transaction which inserts data and one that deletes it and finally a non-trivial query which demonstrates multiple joins, group functions, qualifiers and correlation variables. Relational Database Design The hallmark of the relational database is that when one table changes in the database all other tables are updated and are current. 1. Specification of the functionality of the system The Relational Database specification required nine table in the following relationships. A table Textbook with a many-to-many relationship through a junction table (outside join) to the table Module through the Module_Detail table. A many-to-many relationship is required between Module and Field through a join table (outer join or junction table) Field_Detail. A many-to-one relationship must be made between the Session table ( a group of the Lecture table and the Practical table) and the Module table. The Lecture table and the Practical table are both join tables which allow the necessary one to many relationship with the Tutor table and the Session table and the many-to-many relationship between Tutor and Session table. A one-to-many relationship should be constructed between the Tutor table and the Lecture table. Finally, a many-to-many relationship between the table Tutor and the table Practical must be created. The versions of MySQL used for this exercise the relationship of many entities to many must be made through an outer join, represented by the join table. This is displayed in the figure below in paragraph 2.b. These specifications have been duplicated in the attached appendices with the required SQL code. 2. Logical Data Model a. The schematic describing the logical data model is illustrated in paragraph 2. b. below. It uses the American Standard diagrams used to describe functionality and relational integrity found in A Guide to Developing Client/Server SQL Applications, Khoshafian, Setrag, 1992. b. Logical Data Model Schema: c. Appendices: I. Table Definition. a. The Textbook Table. Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 2 to server version: 4.1.12-nt Type 'help;' or 'h' for help. Type 'c' to clear the buffer. mysql> use Relational; Database changed mysql> CREATE TABLE Textbook ( -> Tid INT, -> Chapter INT, -> Mid INT, -> PRIMARY KEY(Tid)); Query OK, 0 rows affected (0.15 sec) mysql> INSERT INTO Textbook VALUES -> (1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5),(6,6,6),(7,7,7),(8,8,8), -> (9,9,9),(10,10,10),(11,11,11),(12,12,12),(13,13,13),(14,14,14), -> (15,15,15),(16,16,16),(17,17,17),(18,18,18),(19,19,19),(20,20,20), -> (21,21,21),(22,22,22),(23,23,23),(24,24,24),(25,25,25),(26,26,26), -> (27,27,27),(28,28,28),(29,29,29),(30,30,30),(31,31,31),(32,32,32); Query OK, 32 rows affected (0.11 sec) Records: 32 Duplicates: 0 Warnings: 0 mysql> DESCRIBE Textbook; +---------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+---------+------+-----+---------+-------+ | Tid | int(11) | | PRI | 0 | | | Chapter | int(11) | YES | | NULL | | | Mid | int(11) | YES | | NULL | | +---------+---------+------+-----+---------+-------+ 3 rows in set (0.07 sec) mysql> SELECT * FROM Textbook; +-----+---------+------+ | Tid | Chapter | Mid | +-----+---------+------+ | 1 | 1 | 1 | | 2 | 2 | 2 | | 3 | 3 | 3 | | 4 | 4 | 4 | | 5 | 5 | 5 | | 6 | 6 | 6 | | 7 | 7 | 7 | | 8 | 8 | 8 | | 9 | 9 | 9 | | 10 | 10 | 10 | | 11 | 11 | 11 | | 12 | 12 | 12 | | 13 | 13 | 13 | | 14 | 14 | 14 | | 15 | 15 | 15 | | 16 | 16 | 16 | | 17 | 17 | 17 | | 18 | 18 | 18 | | 19 | 19 | 19 | | 20 | 20 | 20 | | 21 | 21 | 21 | | 22 | 22 | 22 | | 23 | 23 | 23 | | 24 | 24 | 24 | | 25 | 25 | 25 | | 26 | 26 | 26 | | 27 | 27 | 27 | | 28 | 28 | 28 | | 29 | 29 | 29 | | 30 | 30 | 30 | | 31 | 31 | 31 | | 32 | 32 | 32 | +-----+---------+------+ 32 rows in set (0.38 sec) b. The Module Table. Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 4 to server version: 4.1.12-nt Type 'help;' or 'h' for help. Type 'c' to clear the buffer. mysql> USE Relational Database changed mysql> CREATE TABLE Module ( -> Mid INT, -> Module INT, -> Chapter INT, -> PRIMARY KEY(Mid)); Query OK, 0 rows affected (0.37 sec) mysql> INSERT INTO Module VALUES -> (1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5),(6,6,6),(7,7,7),(8,8,8), -> (9,9,9),(10,10,10),(11,11,11),(12,12,12),(13,13,13),(14,14,14), -> (15,15,15),(16,16,16),(17,17,17),(18,18,18),(19,19,19),(20,20,20), -> (21,21,21),(22,22,22),(23,23,23),(24,24,24),(25,25,25),(26,26,26), -> (27,27,27),(28,28,28),(29,29,29),(30,30,30),(31,31,31),(32,32,32); Query OK, 32 rows affected (0.00 sec) Records: 32 Duplicates: 0 Warnings: 0 mysql> DESCRIBE Module; +---------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+---------+------+-----+---------+-------+ | Mid | int(11) | | PRI | 0 | | | Module | int(11) | YES | | NULL | | | Chapter | int(11) | YES | | NULL | | +---------+---------+------+-----+---------+-------+ 3 rows in set (0.01 sec) mysql> SELECT * FROM Module; +-----+--------+---------+ | Mid | Module | Chapter | +-----+--------+---------+ | 1 | 1 | 1 | | 2 | 2 | 2 | | 3 | 3 | 3 | | 4 | 4 | 4 | | 5 | 5 | 5 | | 6 | 6 | 6 | | 7 | 7 | 7 | | 8 | 8 | 8 | | 9 | 9 | 9 | | 10 | 10 | 10 | | 11 | 11 | 11 | | 12 | 12 | 12 | | 13 | 13 | 13 | | 14 | 14 | 14 | | 15 | 15 | 15 | | 16 | 16 | 16 | | 17 | 17 | 17 | | 18 | 18 | 18 | | 19 | 19 | 19 | | 20 | 20 | 20 | | 21 | 21 | 21 | | 22 | 22 | 22 | | 23 | 23 | 23 | | 24 | 24 | 24 | | 25 | 25 | 25 | | 26 | 26 | 26 | | 27 | 27 | 27 | | 28 | 28 | 28 | | 29 | 29 | 29 | | 30 | 30 | 30 | | 31 | 31 | 31 | | 32 | 32 | 32 | +-----+--------+---------+ 32 rows in set (0.00 sec) c. The Module_Detail Table. mysql> SELECT * FROM Textbook, Module WHERE Textbook.Mid=Module.Mid; +-----+---------+------+-----+--------+---------+ | Tid | Chapter | Mid | Mid | Module | Chapter | +-----+---------+------+-----+--------+---------+ | 1 | 1 | 1 | 1 | 1 | 1 | | 2 | 2 | 2 | 2 | 2 | 2 | | 3 | 3 | 3 | 3 | 3 | 3 | | 4 | 4 | 4 | 4 | 4 | 4 | | 5 | 5 | 5 | 5 | 5 | 5 | | 6 | 6 | 6 | 6 | 6 | 6 | | 7 | 7 | 7 | 7 | 7 | 7 | | 8 | 8 | 8 | 8 | 8 | 8 | | 9 | 9 | 9 | 9 | 9 | 9 | | 10 | 10 | 10 | 10 | 10 | 10 | | 11 | 11 | 11 | 11 | 11 | 11 | | 12 | 12 | 12 | 12 | 12 | 12 | | 13 | 13 | 13 | 13 | 13 | 13 | | 14 | 14 | 14 | 14 | 14 | 14 | | 15 | 15 | 15 | 15 | 15 | 15 | | 16 | 16 | 16 | 16 | 16 | 16 | | 17 | 17 | 17 | 17 | 17 | 17 | | 18 | 18 | 18 | 18 | 18 | 18 | | 19 | 19 | 19 | 19 | 19 | 19 | | 20 | 20 | 20 | 20 | 20 | 20 | | 21 | 21 | 21 | 21 | 21 | 21 | | 22 | 22 | 22 | 22 | 22 | 22 | | 23 | 23 | 23 | 23 | 23 | 23 | | 24 | 24 | 24 | 24 | 24 | 24 | | 25 | 25 | 25 | 25 | 25 | 25 | | 26 | 26 | 26 | 26 | 26 | 26 | | 27 | 27 | 27 | 27 | 27 | 27 | | 28 | 28 | 28 | 28 | 28 | 28 | | 29 | 29 | 29 | 29 | 29 | 29 | | 30 | 30 | 30 | 30 | 30 | 30 | | 31 | 31 | 31 | 31 | 31 | 31 | | 32 | 32 | 32 | 32 | 32 | 32 | +-----+---------+------+-----+--------+---------+ 32 rows in set (0.00 sec) mysql> SELECT * FROM Module_Detail; +-----+-----+---------+ | Tid | Mid | Chapter | +-----+-----+---------+ | 1 | 1 | 1 | | 2 | 1 | 2 | | 3 | 1 | 3 | | 4 | 2 | 4 | | 5 | 2 | 5 | | 6 | 2 | 6 | | 7 | 3 | 7 | | 8 | 3 | 8 | | 9 | 3 | 9 | | 10 | 4 | 10 | | 11 | 4 | 11 | | 12 | 4 | 12 | | 13 | 5 | 13 | | 14 | 5 | 14 | | 15 | 5 | 15 | | 16 | 6 | 16 | | 17 | 6 | 17 | | 18 | 6 | 18 | | 19 | 7 | 19 | | 20 | 7 | 20 | | 21 | 7 | 21 | | 22 | 8 | 22 | | 23 | 8 | 23 | | 24 | 8 | 24 | | 25 | 9 | 25 | | 26 | 9 | 26 | | 27 | 9 | 27 | | 28 | 10 | 28 | | 29 | 10 | 29 | | 30 | 10 | 30 | | 31 | 11 | 31 | | 32 | 11 | 32 | +-----+-----+---------+ 32 rows in set (0.00 sec) mysql> DESCRIBE Module_Detail; +---------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+---------+------+-----+---------+-------+ | Tid | int(11) | | PRI | 0 | | | Mid | int(11) | | PRI | 0 | | | Chapter | int(11) | YES | | NULL | | +---------+---------+------+-----+---------+-------+ 3 rows in set (0.00 sec) d. The Field Table. Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 6 to server version: 4.1.12-nt Type 'help;' or 'h' for help. Type 'c' to clear the buffer. mysql> USE Relational; Database changed mysql> CREATE TABLE Field ( -> Fid INT, -> Chapter INT, -> Mid INT, -> Lecture CHAR(32), -> Practical CHAR(32), -> Tutor CHAR(32), -> PRIMARY KEY(Fid)); Query OK, 0 rows affected (0.11 sec) Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 8 to server version: 4.1.12-nt Type 'help;' or 'h' for help. Type 'c' to clear the buffer. mysql> USE Relational; Database changed mysql> DESCRIBE Field; +-----------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+----------+------+-----+---------+-------+ | Fid | int(11) | | PRI | 0 | | | Chapter | int(11) | YES | | NULL | | | Mid | int(11) | YES | | NULL | | | Lecture | char(32) | YES | | NULL | | | Practical | char(32) | YES | | NULL | | | Tutor | char(32) | YES | | NULL | | +-----------+----------+------+-----+---------+-------+ 6 rows in set (0.00 sec) mysql> SELECT * FROM Field; +-----+---------+------+--------------+--------------+----------------+ | Fid | Chapter | Mid | Lecture | Practical | Tutor | +-----+---------+------+--------------+--------------+----------------+ | 1 | 1 | 1 | Fortran | Fortran | Mr. Gomez | | 2 | 2 | 2 | Lisp | Lisp | Mr. Smith | | 3 | 3 | 3 | Common Lisp | Common Lisp | Mr. McClaskey | | 4 | 4 | 4 | Java | Java | Ms. Schwabach | | 5 | 5 | 5 | C | C | Mr. Valentino | | 6 | 6 | 6 | C++ | C++ | Ms. Racine | | 7 | 7 | 7 | Perl | Perl | Mr. Jakobowski | | 8 | 8 | 8 | Python | Python | Ms. Gloria | | 9 | 9 | 9 | Visual Basic | Visual Basic | Mr. Gomez | | 10 | 10 | 10 | Delphi | Delphi | Mr. Smith | | 11 | 11 | 11 | SmallTalk | SmallTalk | Mr. McClaskey | | 12 | 12 | 12 | Cobol | Cobol | Ms. Schwabach | | 13 | 13 | 13 | Pascal | Pascal | Mr. Valentino | | 14 | 14 | 14 | PHP | PHP | Ms. Racine | | 15 | 15 | 15 | HTML | HTML | Mr. Jakobowski | | 16 | 16 | 16 | XHTML | XHTML | Ms. Gloria | | 17 | 17 | 17 | SMGL | SGML | Mr. Gomez | | 18 | 18 | 18 | EMACS | EMACS | Mr. Smith | | 19 | 19 | 19 | XEMACS | XEMACS | Mr. McClaskey | | 20 | 20 | 20 | XML | XML | Ms. Schwabach | +-----+---------+------+--------------+--------------+----------------+ 20 rows in set (0.00 sec) e. The Field_Detail Table. Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 9 to server version: 4.1.12-nt Type 'help;' or 'h' for help. Type 'c' to clear the buffer. mysql> USE Relational; Database changed mysql> CREATE TABLE Field_Detail ( -> Fid INT, -> Mid INT, -> Chapter INT, -> PRIMARY KEY(Fid,Mid)); Query OK, 0 rows affected (0.07 sec) mysql> INSERT INTO Field_Detail VALUES -> (1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5),(6,6,6),(7,7,7),(8,8,8), -> (9,9,9),(10,10,10),(11,11,11),(12,12,12),(13,13,13),(14,14,14), -> (15,15,15),(16,16,16),(17,17,17),(18,18,18),(19,19,19),(20,20,20), -> (21,21,21),(22,22,22),(23,23,23),(24,24,24),(25,25,25),(26,26,26), -> (27,27,27),(28,28,28),(29,29,29),(30,30,30),(31,31,31),(32,32,32); Query OK, 32 rows affected (0.00 sec) Records: 32 Duplicates: 0 Warnings: 0 mysql> DESCRIBE Field_Detail; +---------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+---------+------+-----+---------+-------+ | Fid | int(11) | | PRI | 0 | | | Mid | int(11) | | PRI | 0 | | | Chapter | int(11) | YES | | NULL | | +---------+---------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> SELECT * FROM Field_Detail; +-----+-----+---------+ | Fid | Mid | Chapter | +-----+-----+---------+ | 1 | 1 | 1 | | 2 | 2 | 2 | | 3 | 3 | 3 | | 4 | 4 | 4 | | 5 | 5 | 5 | | 6 | 6 | 6 | | 7 | 7 | 7 | | 8 | 8 | 8 | | 9 | 9 | 9 | | 10 | 10 | 10 | | 11 | 11 | 11 | | 12 | 12 | 12 | | 13 | 13 | 13 | | 14 | 14 | 14 | | 15 | 15 | 15 | | 16 | 16 | 16 | | 17 | 17 | 17 | | 18 | 18 | 18 | | 19 | 19 | 19 | | 20 | 20 | 20 | | 21 | 21 | 21 | | 22 | 22 | 22 | | 23 | 23 | 23 | | 24 | 24 | 24 | | 25 | 25 | 25 | | 26 | 26 | 26 | | 27 | 27 | 27 | | 28 | 28 | 28 | | 29 | 29 | 29 | | 30 | 30 | 30 | | 31 | 31 | 31 | | 32 | 32 | 32 | +-----+-----+---------+ 32 rows in set (0.00 sec) f. The Session Table. Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 11 to server version: 4.1.12-nt Type 'help;' or 'h' for help. Type 'c' to clear the buffer. mysql> USE Relational; Database changed mysql> CREATE TABLE Lecture ( -> Id INT, -> Tutor_id INT, -> Lecture CHAR(32), -> PRIMARY KEY(Id,Tutor_id)); Query OK, 0 rows affected (0.07 sec) mysql> INSERT INTO Lecture VALUES -> (1,1,'Fortran'),(2,2,'Lisp'),(3,3,'Common Lisp'),(4,4,'Java'), -> (5,5,'C'); Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> DESCRIBE Lecture; +----------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------+------+-----+---------+-------+ | Id | int(11) | | PRI | 0 | | | Tutor_id | int(11) | | PRI | 0 | | | Lecture | char(32) | YES | | NULL | | +----------+----------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> SELECT * FROM Lecture; +----+----------+-------------+ | Id | Tutor_id | Lecture | +----+----------+-------------+ | 1 | 1 | Fortran | | 2 | 2 | Lisp | | 3 | 3 | Common Lisp | | 4 | 4 | Java | | 5 | 5 | C | +----+----------+-------------+ 5 rows in set (0.00 sec) g. The Lecture Table. Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 11 to server version: 4.1.12-nt Type 'help;' or 'h' for help. Type 'c' to clear the buffer. mysql> USE Relational; Database changed mysql> CREATE TABLE Lecture ( -> Id INT, -> Tutor_id INT, -> Lecture CHAR(32), -> PRIMARY KEY(Id,Tutor_id)); Query OK, 0 rows affected (0.07 sec) mysql> INSERT INTO Lecture VALUES -> (1,1,'Fortran'),(2,2,'Lisp'),(3,3,'Common Lisp'),(4,4,'Java'), -> (5,5,'C'); Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> DESCRIBE Lecture; +----------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------+------+-----+---------+-------+ | Id | int(11) | | PRI | 0 | | | Tutor_id | int(11) | | PRI | 0 | | | Lecture | char(32) | YES | | NULL | | +----------+----------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> SELECT * FROM Lecture; +----+----------+-------------+ | Id | Tutor_id | Lecture | +----+----------+-------------+ | 1 | 1 | Fortran | | 2 | 2 | Lisp | | 3 | 3 | Common Lisp | | 4 | 4 | Java | | 5 | 5 | C | +----+----------+-------------+ 5 rows in set (0.00 sec) h. The Practical Table. Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 12 to server version: 4.1.12-nt Type 'help;' or 'h' for help. Type 'c' to clear the buffer. mysql> USE Relational; Database changed mysql> CREATE TABLE Practical ( -> Id INT, -> Practical_id INT, -> Tutor_id INT, -> Practical CHAR(32), -> PRIMARY KEY(Id,Practical_id)); Query OK, 0 rows affected (0.40 sec) mysql> INSERT INTO Practical VALUES -> (1,1,1,'Fortran'),(2,1,2,'Fortran'),(3,1,3,'Fortran'), -> (4,1,4,'Fortran'),(5,2,5,'Lisp'); Query OK, 5 rows affected (0.01 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> DESCRIBE Practical; +--------------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+----------+------+-----+---------+-------+ | Id | int(11) | | PRI | 0 | | | Practical_id | int(11) | | PRI | 0 | | | Tutor_id | int(11) | YES | | NULL | | | Practical | char(32) | YES | | NULL | | +--------------+----------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> SELECT * FROM Practical; +----+--------------+----------+-----------+ | Id | Practical_id | Tutor_id | Practical | +----+--------------+----------+-----------+ | 1 | 1 | 1 | Fortran | | 2 | 1 | 2 | Fortran | | 3 | 1 | 3 | Fortran | | 4 | 1 | 4 | Fortran | | 5 | 2 | 5 | Lisp | +----+--------------+----------+-----------+ 5 rows in set (0.01 sec) i. The Tutor Table. Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 13 to server version: 4.1.12-nt Type 'help;' or 'h' for help. Type 'c' to clear the buffer. mysql> USE Relational; Database changed mysql> CREATE TABLE Tutor ( -> Id INT, -> Tutor CHAR(32), -> PRIMARY KEY(Id)); Query OK, 0 rows affected (0.40 sec) mysql> INSERT INTO Tutor VALUES -> (1,'Mr. Gomez'),(2,'Mr. Smith'),(3,'Mr. McClaskey'),(4,'Ms. Schwabach'), -> (5,'Mr. Valentino'),(6,'Ms. Racine'),(7,'Mr. Jakobowski'), -> (8,'Ms. Gloria'); Query OK, 8 rows affected (0.00 sec) Records: 8 Duplicates: 0 Warnings: 0 mysql> DESCRIBE Tutor; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | Id | int(11) | | PRI | 0 | | | Tutor | char(32) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> SELECT * FROM Tutor; +----+----------------+ | Id | Tutor | +----+----------------+ | 1 | Mr. Gomez | | 2 | Mr. Smith | | 3 | Mr. McClaskey | | 4 | Ms. Schwabach | | 5 | Mr. Valentino | | 6 | Ms. Racine | | 7 | Mr. Jakobowski | | 8 | Ms. Gloria | +----+----------------+ 8 rows in set (0.00 sec) mysql> II. Live Transactions. All live transactions are shown in Appendix I. These consist of the Create Database Relational. The Create Table Textbook and the other nine tables that are created in this appendix with their primary keys. III. A Non-trivial Query. All insert queries and those requiring the creation of junction tables or outside joins for the many-to-many design requirements are in this category. References The MySQL Reference Manual, MySQL 5.0 through 5.0.19, Document generated on: 2006-01-20 . Khoshafian, Setrag, Chan, Arvola, Wong, Anna, Wong, Harry K. T., A Guide to Developing Client/Server SQL Applications, Morgan Kaufmann Publishers, San Francisco, California, 1992. Read More