Question

Question 2

What can you say about the relationships between Courses and Subject, and Courses and Students? Will there be any problems in creating a Relational Database from your ER diagram? If there is, then describe how you will address this and re-draw the ER diagram.

Question 3

Create the DDL statements to create the tables described in your modifies ER diagram in Question 2.

Question 4

In your Database you created in Question 3, assume there is a student record for student

12543 Paul Darbyshire

who is enrolled in the course

BIS Bachelor of Information Systems

It is the beginning of Semester 1 2020. Paul wants to drop all his current subjects for this semester and change courses from the BIS course to the BHR Bachelor of Human Resources course. This is a new course and won’t begin until Semester 2 2020. Paul needs to be enrolled in the following 3 subjects for semester 2 2020

HR101 Introduction to Human Resources HR211 HR for Medium Level Enterprises HR201 HR for Dummies

Discuss any changes you may need to make to your database from Question 3 given this information and provide the SQL statements in order to accomplish the above tasks.

Answer 1

2. Relation between Courses and Subject is Many to Many and relation between Courses and Students is also Many to Many.

Explanation:

One course can contain many subjects and also each subject can be taught in more than one course.

One student can enroll in as many courses as he wants and also each course has many students enrolled.

There will be no problem creating database from the ER diagram.

3. We will create 3 tables. Courses, Subject and Students

Courses table:

create table courses(cid number(5) primary key, cname varchar(40) not null unique);

Subject table:

create table subject(sid number(5) primary key, sname(40) unique,cid number(5) foreign key references courses(cid));

Students table:

create table students(s_id number(5), sname varchar(20),cid number(5) foreign key references courses(cid));