Simple MYSQL database Would someone be able to create a simple MYSQL database co

Question

Simple MYSQL database

Would someone be able to create a simple MYSQL database consisting of 5 tables? Tables: An 'employee detail table' consisting of: employee ID (primary key), first name, surname, email and organisationID (foreign key). A 'business table' consisting of: organisationID (primary key), business name, numOfLocations, numOfEmployees and address. An 'incident table' consisting of: IncidentID (Primary key), date employeeID (foreign key), organisationID (foreign key) and description (varchar). A 'quiz table' consisting of: quizID (primary key), employeeID (foreign key), title, correct_Ans, wrong_Ans, average. And the ‘log table’ which would list the cases stored in the ‘incident table’ as either pending or resolved (boolean).

Explanation / Answer

The CREATE TABLE statements of all the tables are as follows:

CREATE TABLE `Business` (
`Organization_ID` int(11) NOT NULL,
`Business_Name` varchar(15) NOT NULL,
`NumOfLocations` int(11) NOT NULL,
`NumOfEmployees` int(11) NOT NULL,
`Address` varchar(45) NOT NULL,
PRIMARY KEY (`Organization_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `Employee_Details` (
`Employee_ID` int(11) NOT NULL,
`First_Name` varchar(15) NOT NULL,
`Surname` varchar(15) NOT NULL,
`Email` varchar(35) NOT NULL,
`Organization_ID` int(11) NOT NULL,
PRIMARY KEY (`Employee_ID`),
KEY `fk_Employee_Details_idx` (`Organization_ID`),
CONSTRAINT `fk_Employee_Details` FOREIGN KEY (`Organization_ID`) REFERENCES `Business` (`Organization_ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `Incident` (
`Incident_ID` int(11) NOT NULL,
`Date` date NOT NULL,
`Employee_ID` int(11) NOT NULL,
`Organization_ID` int(11) DEFAULT NULL,
`Description` varchar(45) DEFAULT NULL,
PRIMARY KEY (`Incident_ID`),
KEY `fk_Incident_1_idx` (`Employee_ID`),
KEY `fk_Incident_2_idx` (`Organization_ID`),
CONSTRAINT `fk_Incident_1` FOREIGN KEY (`Employee_ID`) REFERENCES `Employee_Details` (`Employee_ID`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_Incident_2` FOREIGN KEY (`Organization_ID`) REFERENCES `Business` (`Organization_ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `Log` (
`Log_ID` int(11) NOT NULL,
`Incident_ID` int(11) NOT NULL,
`Pending/Resolved` tinyint(1) NOT NULL,
PRIMARY KEY (`Log_ID`),
KEY `fk_Log_1_idx` (`Incident_ID`),
CONSTRAINT `fk_Log_1` FOREIGN KEY (`Incident_ID`) REFERENCES `Incident` (`Incident_ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `Quiz` (
`Quiz_ID` int(11) NOT NULL,
`Employee_ID` int(11) NOT NULL,
`Title` varchar(15) NOT NULL,
`Correct_Ans` int(11) NOT NULL,
`Wrong_Ans` int(11) NOT NULL,
`Average` decimal(10,0) NOT NULL,
PRIMARY KEY (`Quiz_ID`),
KEY `fk_Quiz_1_idx` (`Employee_ID`),
CONSTRAINT `fk_Quiz_1` FOREIGN KEY (`Employee_ID`) REFERENCES `Employee_Details` (`Employee_ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Description:

Here, I have created every table as per the column names given.

All the table description are same as the given column names and data types.

Just 2 things I need to mention here:

The first is regarding the log table.

I have created an extra column LogID to uniquely identify every entry of this table.

In this table, you are supposed to store the incident is either pending or resolved.

There is not a in-built BOOL or boolean data type, you need to use TINYINT() with the size 1. So that it can store only 2 values 0 or 1. 0 means false and 1 means true.

The next thing is : ON DELETE CASCADE and ON UPDATE CASCADE

This is written in every foreign key. The reason I wrote this is because: We are defining a foreign key here.

So, it allows only the values that are available in the primary key. Now, if we delete some entry from the primary key, and if that value was referenced by the foreign key i.e. used in the foreign key, its reference is not present in primary key. So, we should also delete all the entries from foreign key also,

For example, in the log table, there is a foreign key on the Incident_ID from the Incident table.

Now, if the Incident was having a primary key value 1201 and it was written in the log also.

Now, if this entry in the Incident table is deleted, there is no reference to 1201 Incident_ID of log table. So, it should also be deleted when we delete that value from primary key.

Do comment if there is any query, Thank you. :)

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.