ITECH1103 Big Data and Analytics Assignment Help

ITECH1103 Big Data and Analytics 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].