DATABASE SYSTEMS
ICT_201
DATABASE SYSTEMS
Assessment_2
Database Design
Group Members:
Prasana Lal Shrestha (CIHE21603)
Kanchan Khadka (CIHE 21596)
Tuya Battulga (CIHE22106)
Table of Contents
Relational Schema
Dependency Diagram (BASIC)
Dependency Diagram In 3rd Normal Form
Crows Foot Entity Relationship Diagram
Database Creation using the ERD in mySQLSample Data entry
QUERIES
The Relational Schema is as follows:
CONTRACT(CLIENT_NUM, CLIENT_NAME, DATE, CONTRACT, CLASS_1, CLASS_2, CLASS_3, CLASS_4, REGION, CONS_NUM_1, CONS_NAME_1, CONS_NUM_2, CONS_NAME_2, CONS_NUM_3, CONS_NAME_3, CONS_NUM_4, CONS_NAME_4)
As the business rules suggests, a client can sign more than a single contract, therefore we need more than just the client number to identify the remaining attributes. In addition, another client might also sign a contract on the same date, so the CLIENT_NUM cannot be used to determine the date of signature.
Furthermore, the same client can sign multiple contracts on the same date or on different dates. The client may do this with the same consultant for all of their contracts or choose a different consultant for different contracts.
Given the consultants have more than one area of expertise, the same consultant may work on different contracts for the same client or for different clients.
The Dependency Diagram:
Dependency Diagram in 3NF:
The relational schema after converting it to 3NF is as follows:
CLIENT(CLIENT_NUM, CLIENT_NAME, REGION_CODE)
CLASS(CLASS_CODE, CLASS_DESCRIPTION)
CONTRACT(CONTR_NUM, CLIENT_CODE, CONTR_DATE, REGION_CODE)
CONSULTANT(CONS_NUM, CONS_NAME, REGION_CODE)
REGION(REGION_CODE, REGION_NAME)
Crows Foot
ENTITY RELATIONSHIP DIAGRAM
D. In below picture, we created database
This one is database table creation
This table for the client
For the consultant
Also, for the consult class table
In this picture we created table for contract
And this picture shows regions table
Another client table
Also another consultant table
SQL Queries
1.SELECT
2. Numeric function
3. Inner join
4. Right join
References
sql - MySQL Multiple Joins in one query?(n.d.). Stack Overflow. Retrieved October 14, 2022, from https://stackoverflow.com/questions/8974328/mysql-multiple-joins-in-one-queryMySQL | Database Files. (2019, August 30). GeeksforGeeks. https://www.geeksforgeeks.org/mysql-database-files/
Normalization in SQL | 1NF, 2NF, 3NF and BCNF in Data Base. (2019, December 31). Besant Technologies. https://www.besanttechnologies.com/normalization-in-sqlHow to create ER diagram for existing MySQL database with MySQL Workbench - MySQL Workbench Tutorials. (n.d.). Dataedo.com. https://dataedo.com/kb/tools/mysql-workbench/create-database-diagram
