Database Concepts Assignment - Database Design and SQL Queries

This assignment solution covers fundamental database concepts, including the relational model, SQL queries, and Entity-Relationship Diagrams (ERDs). The solution begins by creating tables for employees, departments, dependents, and projects using SQL, followed by inserting data into these tables. It then addresses deleting records, emphasizing the importance of foreign key constraints and the use of ON DELETE CASCADE. The solution includes SQL queries to retrieve academic titles, department details, institution and department names, and academic information based on specific criteria. It also covers self-joins and provides an analysis of an ERD, identifying and correcting an error in arrow direction and providing the relational schema. The solution concludes with a discussion of challenges faced during the assignment and references relevant sources.

Student Name
Student Id 1
Assignment 1: Database Concepts
Submitted By
Course
Professor
Date

Student Name
Student Id 2
Answer 1 – The Relational Model
Create Table
1. Employee
SQL> create table Employee (empNo varchar2(5) primary key, givename varchar2(30),
famname varchar2(30), gender char(1), DOB date);
2. Dept
SQL> create table Dept (deptNo varchar2 (5) primary key, name varchar2(30), manager_empNo
varchar2(5) references Employee(empNo));

Student Name
Student Id 3
3. Dependent
SQL> create table Dependent (empNo varchar2(5), name varchar2(30), relationship
varchar2(20), constraint pk_emp primary key (empNo, name));
4. Project

Student Name
Student Id 4
SQL> create table Project (projNo varchar2(5) primary key, description varchar2(50), deptNo
varchar2(5) references Dept(deptNo));
Inserting Data
1. Employee
SQL> Insert into Employee values ('1', 'James', 'Bond', 'M', to_date('23/05/1968',
'dd/mm/yyyy'));
Insert into Employee values ('2', 'Smith', 'Citizen', 'M', to_date('11/01/1977',
'dd/mm/yyyy'));
Insert into Employee values ('3', 'Jane', 'Smith', 'F', to_date('06/03/1998', 'dd/mm/yyyy'));

Student Name
Student Id 5
2. Dept
SQL> Insert into Dept values ('d1', 'Computer Science', '1');
Insert into Dept values ('d2', 'Engineering', '2');
Insert into Dept values ('d3', 'Maths', '3');

Student Name
Student Id 6
3. Dependent
SQL> Insert into Dependent values ('1', 'Andy', 'son');
Insert into Dependent values ('1', 'Sharon', 'daughter');
Insert into Dependent values ('2', 'Aaron', 'son');
4. Project
SQL> Insert into Project values ('p1', 'IT Infrastructure', 'd1');
Insert into Project values ('p2', 'Networking', 'd1');
Insert into Project values ('p3', 'Analysis', 'd3');

Student Name
Student Id 7
3. Deleting Record
While deleting employee from database, it needs to be deleted first from child table. When we
are deleting record directly in employee table without concentrating on child record, it gives the
following error message. This query is the violation of foreign key constraint.

Student Name
Student Id 8
In order to remove this type of violation, we must use ON DELETE CASCADE while creating
the tables. On delete cascade can be used with foreign key which implies that if a record in the
parent table is erased, at that point the corresponding records in the referenced table will
consequently be erased. This is known as a cascade delete in Oracle. On delete cascade can
defined in CREATE TABLE statement or can be used in ALTER TABLE statement. Due to the
cascade delete option, if a row is deleted from parent table, all the corresponding records will
automatically be deleted from the child table. (https://www.techonthenet.com/, 2017).
In order to delete Employee “James Bond”, first we have to delete the record from child table
“Dependent”, and all other child tables like Project, Dept then we can delete the record from
“Employees” table.
SQL> Delete from dependent where EmpNo='1';
SQL> Delete from Project where deptNo='d1';
SQL> Delete from Dept where manager_EmpNo='1';
SQL> Delete from Employee where EmpNo='1';

Student Name
Student Id 9
3. Relation Schema of WorkOn
WorkOn (ProjNo*, EmpNo*, time_period)
SQL> Create table WorkOn (ProjNo varchar2(5), EmpNo varchar2(5), time_period varchar2(5),
constraint pk_proj primary key (ProjNo, EmpNo), constraint fk_proj foreign
key (ProjNo) references Project (ProjNo), constraint fk_emp foreign key (EmpNo)
references Employee EmpNo));

Student Name
Student Id 10
ANSWER 2 - SQL
2.1 List the titles of academics.
SQL> Select distinct (title) from academic order by title;
2.2 List the details of departments that have department name ( deptname ) information in
the current database, in alphabetical order of department name.
SQL> Select * from department where deptname is not null order by deptname ;
2.3) List the institution name and department name of departments in Victoria in the
current database. Output should be sorted in the alphabetical order of institution name
and then department name. Note that the value of “Victoria” for the state attribute can
have variations like “VIC”, “Vic” or “vic”.
SQL> select deptnum, instname, deptname, state from department where state ='VIC' order by
instname, deptname;

Student Name
Student Id 11
2.4) List the first name and last name of academics whose last name starts with “A” and
whose title information is missing.
SQL> Select acnum, title, givename, famname from academic where famname like 'A%' and
title is null;
2.5) How many institutions (identified by instname) contain Computer Science-related
departments? Return the number. Note that Computer Science-related departments are
departments whose name contains the word “Computer” or “Computing” in upper or
lower case letters.
SQL> select count(deptnum) as number_of_department from department where deptname like
'Comput%' or deptname like '%Comput%' ;
2.6) List the panum and title of papers written by the academic named “Lori Prior”. The
list should be in the increasing order of panum.
SQL> select author.panum, paper.title, academic.famname, academic.givename from author,
paper, academic where author.panum=paper.panum and author.acnum=academic.acnum
and academic.famname='Prior' and academic.givename='Lori' order by author.panum;
2.7) List the panum , title , author and acnum for papers. Use the NATURAL JOIN
operator.
SQL> select acnum, panum, title, famname, givename from author NATURAL JOIN academic
NATURAL JOIN paper;
2.8) Return departments that have postcode in the range 3000..3999 or that have the string
“CS” in the description.
SQL> select deptnum, deptname, postcode, descrip from department where descrip like '%CS%'
and postcode between 3000 and 3999;

Student Name
Student Id 12
(https://www.techonthenet.com, 2017).
2.9) Correct Query
SQL> select fieldnum, title from field where fieldnum >= 600 and fieldnum<= 699 order by
fieldnum desc;
2.10) select distinct A1.panum
from Author A1, Author A2
where A1.panum=A2.panum and A1.acnum < A2.acnum;
This concept is called inner self join. A self-join is utilized to join a table to itself, and it is
generally utilized when we have a table with dates and we need to compare one date with
another inside a same table. It's extremely normal to do a self-join when looking at two columns
in a similar table and when comparing two scopes of rows inside a similar table. The above
query retrieve panum where acnum of one row is less than acnum of other rows.. (Burleson, D.,
2015).
ANSWER 3
ERD