Database Design: Description Balance Budget (Lab Report Sample)

Part A TASK 1

1. ERD Diagrams

              TASK 2 Data Dictionary                                                             PART 2 TASK1   Student ID, Name, Address, DOB, Mobile, Service ID, description, budget allocated, budget balance, Officer id, officer name  First Normal form No repeating groups     Student table Student ID, Name, Address, DOB, Mobile, Officer id, officer name Service table Student ID, Service ID, description, budget allocated, budget balance   Second Normal form Remove partial dependencies       Budget (student id, Budget allocated, Budget balance) Service (Student id, Service ID, Service Description)   THIRD NORMAL FORM Remove transitive dependency Student (Student ID, name, Address, DOB, Mobile) Officer(officer ID, Officer name, Student ID)     b) STUDENT TABLE Student ID, Student Name, DOB, Mobile, Address, Email SERVICE TABLE Service ID, Description, Balance Budget OFFICER TABLE Officer Name, Officer ID, Balance Budget     Enrolment Table Student ID, subject code, year, semester Exam Table Student ID, Subject code, Result, Grade Subject Table Subject code, Subject description, course ID, Teacher ID Course Table Course ID, Course Description Teacher Table Teacher ID, Teacher Name, Home phone, Mobile phone, Work phone, Teacher Address, Mentor ID                                   TASK 2

1. Logical data model

  Figure 1: logical data model

1. Schema

create Database ‘assignement 1`; use  ‘assignement 1`;       CREATE TABLE `budget` (   `student id` varchar(30) NOT NULL,   `Budget allocated` varchar(30) NOT NULL,   `Budget balance` varchar(30) NOT NULL );     CREATE TABLE `course` (   `course id` varchar(30) NOT NULL,   `course description` varchar(30) NOT NULL );     CREATE TABLE `enrollment` (   `student id` varchar(30) NOT NULL,   `subject code` varchar(30) NOT NULL,   `year` int(30) NOT NULL,   `semester` varchar(30) NOT NULL,   `enrollmentstatus` varchar(30) NOT NULL );     CREATE TABLE `exam` (   `student id` varchar(30) NOT NULL,   `subject code` varchar(30) NOT NULL,   `result` varchar(30) NOT NULL,   `grade` int(30) NOT NULL );     CREATE TABLE `officer` (   `officername` varchar(30) NOT NULL,   `officerid` varchar(30) NOT NULL ) ;       CREATE TABLE `service` (   `service id` varchar(30) NOT NULL,   `description` varchar(30) NOT NULL,   `student id` varchar(30) DEFAULT NULL,   `officer name` varchar(30) DEFAULT NULL );       CREATE TABLE `student` (   `student id` varchar(30) NOT NULL,   `student name` varchar(30) NOT NULL,   `dob` date NOT NULL,   `mobile` varchar(30) NOT NULL,   `address` varchar(30) NOT NULL,   `email` varchar(30) NOT NULL,   `service id` varchar(30) DEFAULT NULL,   `officername` varchar(30) DEFAULT NULL,   `course id` varchar(30) DEFAULT NULL,   `subject code` varchar(30) DEFAULT NULL,   `teacher id` varchar(30) DEFAULT NULL );     CREATE TABLE `subject` (   `subject code` varchar(30) NOT NULL,   `subjectdescription` varchar(30) NOT NULL,   `teacher id` varchar(30) DEFAULT NULL,   `course id` varchar(30) DEFAULT NULL );   CREATE TABLE `teacher` (   `teacher id` varchar(30) NOT NULL,   `teachername` varchar(30) NOT NULL,   `homephone` int(30) NOT NULL,   `mobilephone` int(30) NOT NULL,   `workphone` int(30) NOT NULL,   `teacheraddress` varchar(30) NOT NULL,   `mentor id` varchar(30) DEFAULT NULL,   `student id` varchar(30) NOT NULL );   ALTER TABLE `budget`   ADD KEY `student id` (`student id`);   ALTER TABLE `course`   ADD PRIMARY KEY (`course id`);   ALTER TABLE `enrollment`   ADD KEY `student id` (`student id`),   ADD KEY `subject code` (`subject code`),   ALTER TABLE `exam`   ADD KEY `student id` (`student id`),   ADD KEY `subject code` (`subject code`),   ALTER TABLE `officer`   ADD PRIMARY KEY (`officername`);   ALTER TABLE `service`   ADD PRIMARY KEY (`service id`),   ADD KEY `student id` (`student id`),   ADD KEY `officer name` (`officer name`);   ALTER TABLE `student`   ADD PRIMARY KEY (`student id`),   ADD KEY `student id` (`student id`),   ADD KEY `service id` (`service id`),   ADD KEY `officername` (`officername`),   ADD KEY `course id` (`course id`),   ADD KEY `subject code` (`subject code`),   ADD KEY `teacher id` (`teacher id`);   ALTER TABLE `subject`   ADD PRIMARY KEY (`subject code`),   ADD KEY `couse id` (`course id`),   ADD KEY `teacher id` (`teacher id`),   ADD KEY `teacher id_2` (`teacher id`),   ADD KEY `couse id_2` (`course id`),   ADD KEY `subject code` (`subject code`),   ADD KEY `course id` (`course id`);   ALTER TABLE `teacher`   ADD PRIMARY KEY (`teacher id`);   ALTER TABLE `budget`   ADD CONSTRAINT `budget_ibfk_1` FOREIGN KEY (`student id`) REFERENCES `student` (`student id`);     ALTER TABLE `enrollment`   ADD CONSTRAINT `enrollment_ibfk_1` FOREIGN KEY (`student id`) REFERENCES `student` (`student id`),   ADD CONSTRAINT `enrollment_ibfk_2` FOREIGN KEY (`subject code`) REFERENCES `subject` (`subject code`);   ALTER TABLE `exam`   ADD CONSTRAINT `exam_ibfk_1` FOREIGN KEY (`student id`) REFERENCES `student` (`student id`),   ADD CONSTRAINT `exam_ibfk_2` FOREIGN KEY (`subject code`) REFERENCES `subject` (`subject code`);   ALTER TABLE `officer`   ADD CONSTRAINT `officer_ibfk_1` FOREIGN KEY (`officername`) REFERENCES `student` (`officername`);   ALTER TABLE `service`   ADD CONSTRAINT `service_ibfk_1` FOREIGN KEY (`student id`) REFERENCES `student` (`student id`),   ADD CONSTRAINT `service_ibfk_2` FOREIGN KEY (`officer name`) REFERENCES `officer` (`officername`);   ALTER TABLE `student`   ADD CONSTRAINT `student_ibfk_1` FOREIGN KEY (`teacher id`) REFERENCES `teacher` (`teacher id`),   ADD CONSTRAINT `student_ibfk_2` FOREIGN KEY (`subject code`) REFERENCES `subject` (`subject code`),   ADD CONSTRAINT `student_ibfk_3` FOREIGN KEY (`course id`) REFERENCES `course` (`course id`);   ALTER TABLE `subject`   ADD CONSTRAINT `subject_ibfk_1` FOREIGN KEY (`teacher id`) REFERENCES `teacher` (`teacher id`),   ADD CONSTRAINT `subject_ibfk_2` FOREIGN KEY (`course id`) REFERENCES `course` (`course id`);    

1.  Create database in mysql

Here is the exported database code:   create Database `assignement1`; use  `assignement1`;     CREATE TABLE `budget` (   `student id` varchar(30) NOT NULL,   `Budget allocated` varchar(30) NOT NULL,   `Budget balance` varchar(30) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;   -- --------------------------------------------------------   -- -- Table structure for table...