Database Concepts: Relational Model, SQL, and ER Modeling Analysis

This document presents a comprehensive solution to a database concepts assignment, addressing various aspects of database management. The solution begins with an analysis of the relational model, exploring primary key constraints and data integrity. It then delves into relational database design, providing SQL queries to retrieve customer and account details, along with CREATE TABLE and INSERT INTO statements to populate the database. Furthermore, the assignment covers SQL queries on the DEPARTMENT, ACADEMIC, PAPER, AUTHOR, FIELD, and INTEREST tables, including listing department names, counting research fields, ordering institution and department names, and identifying academics based on specific criteria. The solution also addresses errors in SQL queries and concludes with an ER model for a cookbook example, demonstrating a thorough understanding of database concepts and practical application.

Running head: DATABASE CONCEPTS
Database concepts
Name of the student
Name of the University

1DATABASE CONCEPTS
Question 1. The Relational model
The “Customer” relational database schema is as follows:
Customer (Customer_no, name, e_mail)
Order (Order_no, Order_date, Handling_cost)
Product (Product_no, Product_Name, List_Price)
Makes (customer_no*, Order_no* )
Includes (Order_no*, Product_no*, unit_price, quantity )
1.1. Does the primary key (customer-no, order-no) ensure that each customer orders a
particular product? Explain your answer.
Alone Customer_no and Order_no cannot form a primary key. Since multiple order can be
placed by individual customers and a same order can be placed by multiple customer. Hence
multiple row with same Customer_no and same Order_no is present in the table. However,
Customer_no and Order_no both can be used to form a primary key which enmsure that each
customer purchase a particular product.
1.2. Can two products have the same name? Can two products of the same order have the
same name? Explain your answer using the data integrity constraints on the Dependent
table.
Yes, two products can have the same name. Suppose one product has the discount option in
some order, but same product does not contain any discount in other order option. It ensure
the null values included in the individual attributes in the table.

2DATABASE CONCEPTS
1.3. It is possible that a customer does not make any product? Explain you answer.
It is possible that a customer does not make any product. This means that the customer does
not place any order that contains a single product or multiple products. The left join operation
performs join between both tables Customer and Order, which contains all the records from
left table (Customer), even if the right table (Order table) contains no matching tuples, pads
those tuples with null values.
1.4. Can the below SQL statement be successfully executed? Explain your answer.
INSERT INTO Makes VALUES(6, ‘Dallas’); //’Dallas’ is not in Order
The below statement cannot be successfully executed. Since the data type of the attribute
order_no is defined as number in the Order table, and the value for that attribute is given as a
varchar type. Hence, the sql shows an error message of “invalid number”.
Question 2. Relational database design
Table 1. Data for the ABC Big Bank
2.1 Relational Schema:
Bank_Customer(CUSOMERID, FNAME, LNAME, DOB, POSTCODE,
MOBILE_NUMBER, EMAIL)
Account_Detail(ACCOUNTNO, BALANCE)
Transaction(CUSOMERID*, ACCOUNTNO*)
List the account details for a customer.

3DATABASE CONCEPTS
select account_detail.accountno, balance, fname, CUSOMERID from account_detail inner
join bank_customer on account_detail.accountno = bank_customer.account_no where
cusomerID = 'C005' ;
Find the customer details for an account.
select * from Bank_Customer where ACCOUNT_NO = 'Acc005';
2.2 CREATE TABLE statement:
Bank_Customer Table:
CREATE TABLE Bank_Customer
(
CUSOMERID varchar2(8) NOT NULL,
FNAME varchar2(20),
LNAME varchar2(20),
DOB Date,

4DATABASE CONCEPTS
CITY varchar2(20),
POSTCODE varchar2(10),
MOBILE_NUMBER Number,
EMAIL varchar2(100),
primary key ( CUSOMERID)
);
Account_Detail Table:
CREATE TABLE Account_Detail(
ACCOUNTNO varchar2(20) primary key,
BALANCE varchar2(20) );
Transaction Table:
CREATE TABLE Transaction(
CUSOMERID varchar2(8) ,
ACCOUNTNO varchar2(20),
FOREIGN KEY (CUSOMERID) REFERENCES CUSTOMER_DETAIL(CUSOMERID),
FOREIGN KEY (ACCOUNTNO ) REFERENCES Account_Detail(ACCOUNTNO ))
2.3. INSERT INTO statement:
The SQL insert into statement is used for populate data within the tables.

5DATABASE CONCEPTS
Inserting data into Customer_Detail table:
insert into Customer_detail values ('C001','Chris','Evans','05-OCT-
85','Sydney','78900',89765545900, '[e-mail]')
insert into Customer_detail values ('C002','Ivan','Bayross','24-JUL-74','New
York','68901',90765545945,'[e-mail]')
insert into Customer_detail values ('C003','Joe','Scott','20-JUL-
74','Sydney','78900',89765545990', '[e-mail]')
insert into Customer_detail values ('C004','Calum','Scott','15-OCT-75','New
York','68901',90765545909, '[e-mail]')
Inserting data into Account_Detail table:
insert into Account_Detail values ('Acc001','1,00,000.00')
insert into Account_Detail values ('Acc002','1,50,000.00')
insert into Account_Detail values ('Acc003','2,20,000.00')
insert into Account_Detail values ('Acc004','2,50,000.00')
insert into Account_Detail values ('Acc005','9,50,000.00')
insert into Account_Detail values ('Acc006','5,50,000.00')
insert into Account_Detail values ('Acc007','3,67,000.00')
insert into Account_Detail values ('Acc008','2,90,000.00')
insert into Account_Detail values ('Acc009','4,80,000.00')

6DATABASE CONCEPTS
insert into Account_Detail values ('Acc010','5,75,000.00')
Inserting data into Transaction table:
insert into Transaction values ('C001','Acc005')
insert into Transaction values ('C002','Acc002')
insert into Transaction values ('C005','Acc008')
insert into Transaction values ('C003','Acc009')
insert into Transaction values ('C004','Acc001')
insert into Transaction values ('C003','Acc007')
insert into Transaction values ('C005','Acc010')
Question3.SQL
DEPARTMENT(deptnum,descrip,instname,deptname,state,postcode)
ACADEMIC(acnum,deptnum*,famname,givename,initials,title)
PAPER(panum,title)
AUTHOR(panum*,acnum*)
FIELD(fieldnum,id,title)
INTEREST(fieldnum*, acnum*, descrip)
Create Table statement:
create table DEPARTMENTS (
deptnum varchar2(6) primary key,
descrip varchar2(255),

7DATABASE CONCEPTS
instname varchar2(255),
deptname varchar2(255),
state varchar2(255),
postcode number );
create table ACADEMIC (
acnum varchar2(6) primary key,
deptnum REFERENCES DEPARTMENTS (deptnum ),
famname varchar2(255),
givename varchar2(255),
initials varchar2(255),
title varchar2(255));
create table PAPER (
panum varchar2(255),

8DATABASE CONCEPTS
title varchar2(255));
create table AUTHOR(
panum varchar2(255),
acnum varchar2(6));
create table FIELD(
fieldnum varchar2(6) primary key,
id varchar2(20),
title varchar2(20));
create table INTEREST(
fieldnum varchar2(6) references FIELD(fieldnum) ,
acnum varchar2(6) references ACADEMIC (acnum ) ,
descrip varchar2(255));
3.1 List the deptname of all departments in the database.
SELECT DEPTNAME FROM DEPARTMENT;

9DATABASE CONCEPTS
3.2 How many research fields do an academic whose acnum=100. Return the total number.
SELECT COUNT(ACNUM) FROM ACADEMIC WHERE DEPTNUM = 100;
3.3 List in alphabetical order the institution name (instname) and department name
(deptname) of all academic.
SELECT ACADEMIC.INITIALS, DEPARTMENTS.DEPTNAME FROM ACADEMIC
INNER JOIN DEPARTMENTS ON ACADEMIC.DEPTNUM=
DEPARTMENTS.DEPTNUM ORDER BY INITIALS, DEPTNAME;

10DATABASE CONCEPTS
3.4 Return the famname and givename of academics whose deptnum is in the range
[200..299] or whose fname or givename start with ‘T’.
SELECT FAMNAME, GIVENAME FROM ACADEMIC WHERE FAMNAME LIKE 'T%'
OR GIVENAME LIKE '%T%';
3.5 List the acnum of academics having published at least two papers.
SELECT DEPTNUM FROM DEPARTMENTS WHERE DEPTNUM IN (SELECT
DEPTNUM FROM ACADEMIC GROUP BY DEPTNUM HAVING
COUNT(DEPTNUM)>1);

11DATABASE CONCEPTS
3.6 List in alphabetical order the famname, givename of academics who has interests in
Databases. Note: the values for “Databases” include “DB” or “Database”.
SELECT ACNUM FROM INTEREST WHERE LOWER(DESCRIP) LIKE '%DATA%';
3.7 Find academics who have a Professor title. Give their famname and givename.
SELECT FAMNAME, GIVENAME FROM ACADEMIC WHERE TITLE LIKE 'PROFESSOR';
3.9 The below query is meant to list the paper number (panum) and title of papers whose
panum is between 100 and 200. But it has errors.