Question

This is an entire assignment that I need to be built into an ERD format, please assist me, thank you in advance.

Answer 1

ERD nothing but Entity Relationship Diagram Format Used to demonstrate the Relationship and co-relations between different attributes of the created db tables.

As per the given problem Statement , Below Tables to be created in SQL database Repository

First will create a database with name 'testDB' using SQL Command 'CREATE DATABASE testDB;'

execute the command in SQL Command Window this will create New SQL Database 'testDB'

After creating Database create the needed tables

Table 1: Student to hold attributes like 'the matriculation number, first name, last name, home address, data of birth, sex, category, nationality, smoker, special needs, additional comments, current Status, department'

with the above listed attributes will create new table Student under newly created SQL Databse 'testDB'

CREATE TABLE Student

(matriculation number INTEGER NOT NULL,first name VARCHAR(3000), last name VARCHAR(3000), home address VARCHAR(4000), data of birth VARCHAR(100), sex VARCHAR(15), category VARCHAR(40), nationality VARCHAR(100), smoker BIT DEFAULT 0, special needs VARCHAR(1000), additional comments VARCHAR(4000), current Status VARCHAR(500), department VARCHAR(100)',PRIMARY KEY(matriculation number));

Ran the above command to create Table Student

Every Student is assigned to a Staff Who acts as an advisor, Advisor Table contains the below attributes and create table 'Advisor' with below SQL Command

CREATE TABLE Advisor

(name VARCHAR(1000), position VARCHAR(2000),department name VARCHAR(1000), phone INTEGER, room number INTEGER);

Ran the above command to create Table Advisor.

Advisor can be a Staff to one or more Students and all Students activities are monitered (or) tracked by the Advisor

Students who are studing in college can rent a room @ 'hall_of_residence' or @ 'student_flats' at one time they can be part of one residence only (i.e) either part of 'hall_of_residence' or either part of 'student_flats'

Illustrated the SQL Comments to create tables 'hall_of_residence' and 'student_flats'

CREATE TABLE hall_of_residence

(place number INTEGER NOT NULL,name VARCHAR(3000),Address VARCHAR(4000),monthly rent INTEGER, PRIMARY KEY (place number));

Ran the above command to create Table hall_of_residence.

CREATE TABLE student_flats

(flat number INTEGER NOT NULL, flat address VARCHAR(3000), bedroom INTEGER,monthly rent INTEGER, PRIMARY KEY (flat number ));

Ran the above command to create Table student_flats.

Leases Agreements are made between the Students with needed information (like room number, how many years thery are going to stay etc.,)

Illustrated below how to create table leases

CREATE TABLE leases

(lease number INTEGER NOT NULL, lease duration INTEGER, student name VARCHAR(3000), matriculation number INTEGER NOT NULL, place number INTEGER NOT NULL, flat number INTEGER NOT NULL, Address VARCHAR(4000), flat address VARCHAR(3000), lease start date VARCHAR (500), lease end date VARCHAR(500), PRIMARY KEY (lease number), FOREIGN KEY (matriculation number) REFERENCES Student(matriculation number), FOREIGN KEY (place number) REFERENCES hall_of_residence(place number), FOREIGN KEY (flat number) REFERENCES student_flats(flat number), FOREIGN KEY (Address) REFERENCES hall_of_residence(Address), FOREIGN KEY (flat address) REFERENCES student_flats(flat address));

Use the above SQL Command to create table leases

Foreign Key references are used since all sttributes mentioned in leases table is already created as part of eariler created SQL tables so no need to have a new reference, instead use Foreign Key References as show above.

As mentioned above create remaining tables

Create Tables ' invoice, accomaondation staff, student flat inspections and next-of-kin ' using below SQL commands

CREATE TABLE invoice

(invoice number INTEGER NOT NULL, lease number INTEGER NOT NULL, payment due INTEGER, student name VARCHAR(3000), matriculation number INTEGER NOT NULL, place number INTEGER NOT NULL, flat number INTEGER NOT NULL, Address VARCHAR(4000), flat address VARCHAR(3000), method of pay VARCHAR(500), FOREIGN KEY (lease number) REFERENCES leases(lease number), FOREIGN KEY (matriculation number) REFERENCES Student(matriculation number), FOREIGN KEY (place number) REFERENCES hall_of_residence(place number), FOREIGN KEY (flat number) REFERENCES student_flats(flat number), FOREIGN KEY (Address) REFERENCES hall_of_residence(Address), FOREIGN KEY (flat address) REFERENCES student_flats(flat address));

CREATE TABLE Accomadation_Staff

(name VARCHAR(3000), Address VARCHAR(4000), date of birth VARCHAR(200), sex VARCHAR(20), position VARCHAR(1000), location VARCHAR(1000));

CREATE TABLE Student_Flat_Inspections

(inspection_data VARCHAR(200), inspection_staff_name VARCHAR(3000), additional_comments VARCHAR (4000));

CREATE TABLE Next_of_Kin

(name VARCHAR(2000), relationship VARCHAR(1000), address VARCHAR(2000), phone INTEGER);

Use the above SQL Comments to create tables 'invoice, accommodation staff, student flat inspections and next-of-kin'

After creating all these table , then will create ERD to illustrate the co-relation between all created tables