Big Data and Analytics Assignment Help Archives - Moodle Monkey Assignment Help

Create a new database.

— phpMyAdmin SQL Dump

— version 5.0.2

— https://www.phpmyadmin.net/

—

— Host: 127.0.0.1

— Generation Time: Apr 23, 2020 at 06:55 PM

— Server version: 10.4.11-MariaDB

— PHP Version: 7.4.4

SET SQL_MODE = “NO_AUTO_VALUE_ON_ZERO”;

START TRANSACTION;

SET time_zone = “+00:00”;

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8mb4 */;

—

— Database: `myuniversity`

—

CREATE DATABASE IF NOT EXISTS `myuniversity` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

USE `myuniversity`;

Create the tables

—

— Indexes for dumped tables

—

— Indexes for table `class`

—

ALTER TABLE `class`

ADD PRIMARY KEY (`classID`) USING BTREE,

ADD KEY `FK2` (`LectureID`),

ADD KEY `FK1` (`CourseID`),

ADD KEY `FK3` (`RoomID`);

—

— Indexes for table `course`

—

ALTER TABLE `course`

ADD PRIMARY KEY (`CourseID`);

—

— Indexes for table `lecturer`

—

ALTER TABLE `lecturer`

ADD PRIMARY KEY (`LecturerID`),

ADD UNIQUE KEY `LecturerID` (`LecturerID`);

—

— Indexes for table `room`

—

ALTER TABLE `room`

ADD PRIMARY KEY (`RoomID`),

ADD UNIQUE KEY `RoomID` (`RoomID`);

—

— AUTO_INCREMENT for dumped tables

—

— AUTO_INCREMENT for table `class`

—

ALTER TABLE `class`

MODIFY `classID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7129;

—

— Constraints for dumped tables

—

— Constraints for table `class`

—

ALTER TABLE `class`

ADD CONSTRAINT `FK1` FOREIGN KEY (`CourseID`) REFERENCES `course` (`CourseID`),

ADD CONSTRAINT `FK2` FOREIGN KEY (`LectureID`) REFERENCES `lecturer` (`LecturerID`),

ADD CONSTRAINT `FK3` FOREIGN KEY (`RoomID`) REFERENCES `room` (`RoomID`);

—

— Metadata

—

USE `phpmyadmin`;

—

— Metadata for table class

—

— Metadata for table course

—

— Metadata for table lecturer

—

— Metadata for table room

—

— Metadata for database myuniversity

—

— Dumping data for table `pma__pdf_pages`

—

INSERT INTO `pma__pdf_pages` (`db_name`, `page_descr`) VALUES

(‘myuniversity’, ‘schema’);

SET @LAST_PAGE = LAST_INSERT_ID();

—

— Dumping data for table `pma__table_coords`

—

INSERT INTO `pma__table_coords` (`db_name`, `table_name`, `pdf_page_number`, `x`, `y`) VALUES

(‘myuniversity’, ‘class’, @LAST_PAGE, 402, 143),

(‘myuniversity’, ‘course’, @LAST_PAGE, 638, 31),

(‘myuniversity’, ‘lecturer’, @LAST_PAGE, 640, 137),

(‘myuniversity’, ‘room’, @LAST_PAGE, 651, 231);

—

— Dumping data for table `pma__pdf_pages`

—

INSERT INTO `pma__pdf_pages` (`db_name`, `page_descr`) VALUES

(‘myuniversity’, ‘schema2’);

SET @LAST_PAGE = LAST_INSERT_ID();

—

— Dumping data for table `pma__table_coords`

—

INSERT INTO `pma__table_coords` (`db_name`, `table_name`, `pdf_page_number`, `x`, `y`) VALUES

(‘myuniversity’, ‘class’, @LAST_PAGE, 402, 143),

(‘myuniversity’, ‘course’, @LAST_PAGE, 638, 31),

(‘myuniversity’, ‘lecturer’, @LAST_PAGE, 640, 137),

(‘myuniversity’, ‘room’, @LAST_PAGE, 651, 231);

Create the relationships between the tables

— ——————————————————–

—

— Table structure for table `class`

—

DROP TABLE IF EXISTS `class`;

CREATE TABLE `class` (

`classID` int(11) NOT NULL,

`CourseID` varchar(8) NOT NULL,

`LectureID` int(11) NOT NULL,

`RoomID` varchar(4) NOT NULL,

`Day` char(4) NOT NULL,

`Time` time(3) NOT NULL,

`Length` int(11) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

—

— RELATIONSHIPS FOR TABLE `class`:

— `CourseID`

— `course` -> `CourseID`

— `LectureID`

— `lecturer` -> `LecturerID`

— `RoomID`

— `room` -> `RoomID`

—

— Dumping data for table `class`

—

INSERT INTO `class` (`classID`, `CourseID`, `LectureID`, `RoomID`, `Day`, `Time`, `Length`) VALUES

(2483, ‘ENG402’, 1234, ‘A24’, ‘mond’, ’11:00:18.000′, 9),

(7128, ‘MAT401’, 7890, ‘B26’, ‘tues’, ’15:00:18.000′, 7);

Viewing the relationships between tables: WampServer also provides a way to view the relationships between the tables.

— ——————————————————–

—

— Table structure for table `course`

—

DROP TABLE IF EXISTS `course`;

CREATE TABLE `course` (

`CourseID` varchar(9) NOT NULL,

`CourseName` text NOT NULL,

`Description` text NOT NULL,

`CreditPoints` int(11) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

—

— RELATIONSHIPS FOR TABLE `course`:

—

— Dumping data for table `course`

—

INSERT INTO `course` (`CourseID`, `CourseName`, `Description`, `CreditPoints`) VALUES

(‘ENG402’, ‘ENGLISH’, ‘English Literature class.’, 10),

(‘MAT401’, ‘MATHS’, ‘Mathematical class’, 10);

Inserting data into a table

— ——————————————————–

—

— Table structure for table `lecturer`

—

DROP TABLE IF EXISTS `lecturer`;

CREATE TABLE `lecturer` (

`LecturerID` int(11) NOT NULL,

`LecturerFirstname` text NOT NULL,

`LecturerSurname` text NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

—

— RELATIONSHIPS FOR TABLE `lecturer`:

—

— Dumping data for table `lecturer`

—

INSERT INTO `lecturer` (`LecturerID`, `LecturerFirstname`, `LecturerSurname`) VALUES

(1234, ‘Evan’, ‘Woods’),

(7890, ‘Olivia’, ‘Patrick’);

— ——————————————————–

—

— Table structure for table `room`

—

DROP TABLE IF EXISTS `room`;

CREATE TABLE `room` (

`RoomID` varchar(4) NOT NULL,

`RoomType` text NOT NULL,

`Building` varchar(4) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

—

— RELATIONSHIPS FOR TABLE `room`:

—

— Dumping data for table `room`

—

INSERT INTO `room` (`RoomID`, `RoomType`, `Building`) VALUES

(‘A24’, ‘Single’, ‘ABC’),

(‘B26’, ‘Double’, ‘xyz’);

References

Dev.mysql, 2020. About Creating and Selecting a Database. Dev.mysql. Available at – https://dev.mysql.com/doc/refman/8.0/en/creating-database.html. [Accessed on – 25 April 2020].
Digitalocean.com, 2020. About A Basic MySQL Tutorial. Digitalocean.com. Available at – https://www.digitalocean.com/community/tutorials/a-basic-mysql-tutorial. [Accessed on – 25 April 2020].
Docs.kony, 2020. About Database Setup Guide-MySQL. Docs.kony. Available at – https://docs.kony.com/5_0/docs/databasedocuments/mysql/KonyDatabaseSetupGuide-MySQL.pdf. [Accessed on – 25 April 2020].
Docs.oracle, 2020. About MySQL Database Installation and Configuration for Advanced Management Console. Docs.oracle. Available at – https://docs.oracle.com/javacomponents/advanced-management-console-2/install-guide/mysql-database-installation-and-configuration-advanced-management-console.htm#JSAMI116. [Accessed on – 25 April 2020].
Downloads.mysql, 2020. About MySQL Tutorial. Downloads.mysql. Available at – https://downloads.mysql.com/docs/mysql-tutorial-excerpt-5.7-en.pdf. [Accessed on – 25 April 2020].