SQL Operations and Database Systems

This assignment involves solving SQL queries, database operations, and management problems. It includes creating tables, inserting data, querying data, and managing databases using SQL. The questions cover various aspects of database systems, including relational algebra, table operations, and database design. The assignment requires students to apply their knowledge of SQL and database systems to solve practical problems.

Running head: DATABASE MANAGEMENT SYSTEM
Database Management System
Name of student
Name of University
Author’s Note

1DATABASE MANAGEMENT SYSTEM
Table of Contents
1. Concept of Relational Database...................................................................................................2
a) Relational Data Structure:.......................................................................................................2
b) About Attributes and Tuples:..................................................................................................2
c) Rows, Columns and Tables:....................................................................................................3
d) View:.......................................................................................................................................4
2. Features of a Relational and Non-Relational Database Management System:...........................5
3. (a) DML codes to create:............................................................................................................8
(b) DML codes to insert:............................................................................................................10
(c) SQL integrity features:.........................................................................................................11

2DATABASE MANAGEMENT SYSTEM
1. Concept of Relational Database
a) Relational Data Structure:
Figure 1: Relation Diagram
Source: Author
b) About Attributes and Tuples:
In the Student Relation of figure 1, tuple is a row. Here, it has 5 tuples with 6 values that is
called as 6 tuples. In general, n- tuples has a list ordered of values n. If t1 is refer to first tuple
variable for the relation STUDENT, then t1 = <S10010, Chan Wai Yee, 56A, Jalan 2/14, Taman
Midah, kuala Lampur, 012-3256780, F, [e-mail]>, similarly for the other. Tuples may
appear in sorted or unsorted order. The relations of the order remain the same and give same
information or meaning.

3DATABASE MANAGEMENT SYSTEM
Attribute is the relation characteristic of a column. In figure 1, the relation of STUDENT
consists of 7 attributes namely, id (the student ID), sName, Address, State, Contact Number, Sex,
Email.
Similarities
The relation tuple are similar to the file records, and attributes are field that are analogues
(McGinnes and Kapros 2015).
Difference
The model of relational database is logically related to tables that contain data. Attribute is
a column were particular kind of data are kept; tuple is a row that holds the particular event or
entity of the relation data.
c) Rows, Columns and Tables:
In a relational database concept, tables have a rows and a column. In the table, each of the rows
will represent the related values of the collection (Keith et al. 2018). The information of the object
is hold in the database to represent in the table. In any specific table, data has one kind of objects
called the entities. Table is actually a concept of the data and relation is the concept of relational
model.
Now in a table each of the rows represents one object called the entity that is the collection of the
related values. If the diagram of figure 1 is considered, it has the data of one customer in one of the
row in Student table. Thus, row has considered as a database concept and tuple as the concept of
relational model.

4DATABASE MANAGEMENT SYSTEM
Additionally, in a table each of the columns holds some kind of data. Each of the data is describe
by some column name. For example, in the student table there are sName, Address, State and
many more. Thus, column has considered as a database concept and attribute as the concept of
relational model.
d) View:
A View is a set of queries for virtual tables that holds data from one or more tables and
stored in a database in the form of an object (Learning Views In SQL Server 2018). This has done
for the security purpose that restricts the user to view certain columns and rows. It is better
explained in the below given example:
Figure 2: Example of View
Source: (c-sharpcorner 2018)
Advantages of Views

5DATABASE MANAGEMENT SYSTEM
Independence of logical data: The database tables and application can be independent
through view.
Data integrity: DBMS checks the data has accessed through view that ensures to meet
the constraints of the specified integrity.
Consistency: In the database, an unchanged, consistent image can be present by view for
the structured database.
Structural simplicity: the user can make sense of the personalized view of the structure
of the database that has presented through a set of virtual tables.
Query Simplicity: Queries of multiple tables can be drawn into single table through view
Security: The user can access the database through an authorized set of views that contain
some specific data.
2. Features of a Relational and Non-Relational Database Management System:
Features RDBMS Non-RDBMS
1. They are structured in a
very organized way.
Example, phone book in
which addresses and phone
numbers are stored
They are distributed and
document-oriented.
Example, A file folders that
has everything from phone
number to address of a
person, likes on facebook
and preferences of online

6DATABASE MANAGEMENT SYSTEM
shopping
2. SQL (Structured Query
Language) is used to query
and maintain the database
Data models are Flexible
3. Data provided are stored in
the tables
Types of databases for
NoSql are: Key-Value
Store,
Column-based Store,
Document-based Store-It,
Graph-based
4. Data are stored in the form
of rows and columns
The Key-Value Store has
Tables with Big Hash.
Example Amazon S3,
Riak.
5. It provide a primary key
facility to identify the row
uniquely
Each block of the storage of
Column-based Store data
from only one of the
column. Example, HBase,
Cassandra
6. For a quick retrieval of the
data, indexes are created
The Document-based Store
is a database that stores
tagged elements type of
documents

7DATABASE MANAGEMENT SYSTEM
7. Shares common column
through primary and foreign
key in more than two tables.
The Graph-based is a
network database. Data are
stored and represented using
nodes and edges
8. It ensures the compliancy of
ACID( Atomicity,
Consistency, Isolation,
Durability)
Data are easily spread to
multiple servers using
storage and cloud
computing.
Development are rapid
9. The data is unchanging and
structured
It often have no structure or
little structure and the data
that are store contains large
volumes.
10. Examples of available
database in market
MySQL, Oracle,
MA SQL Server,
IMB DB2,
Microsoft Azure,
Sybase,
MarlaDB, PostgreSQL
MongoDB,
Apache’s CouchDB,
HBase,
Oracle NoSQL,
Apache’s Cassandra DB,
Riak

8DATABASE MANAGEMENT SYSTEM
3. (a) DML codes to create:
Book:
CREATE TABLE `book` (
`id` int(11) NOT NULL,
`title` varchar(255) NOT NULL,
`author` varchar(150) NOT NULL,
`publisher` varchar(150) NOT NULL,
`pubYear` year(4) NOT NULL,
`isbnNo` int(11) NOT NULL,
PRIMARY KEY (id))
Staff:
CREATE TABLE `staff` (
`id` int(11) NOT NULL,
`sName` varchar(100) NOT NULL,
`address` varchar(255) NOT NULL,
`state` varchar(100) NOT NULL,
`contactNo` bigint(20) NOT NULL,

9DATABASE MANAGEMENT SYSTEM
`salary` int(11) NOT NULL,
`joinedDate` varchar(50) NOT NULL,
`dept` varchar(10) NOT NULL,
`position` varchar(30) NOT NULL,
`sex` enum('M','F') NOT NULL,
PRIMARY KEY (id))
Student:
CREATE TABLE `student` (
`id` varchar(10) NOT NULL,
`sName` varchar(150) NOT NULL,
`Address` varchar(255) NOT NULL,
`state` varchar(50) NOT NULL,
`contactNo` varchar(15) NOT NULL,
`sex` enum('M','F') NOT NULL,
`email` varchar(255) NOT NULL,
PRIMARY KEY (id)
)

10DATABASE MANAGEMENT SYSTEM
Borrow:
CREATE TABLE `borrow` (
`studentID` int(11) NOT NULL,
`bookID` int(11) NOT NULL
)
(b) DML codes to insert:
Staff:
INSERT INTO `staff` (`id`, `sName`, `address`, `state`, `contactNo`, `salary`, `joinedDate`, `dept`,
`position`, `sex`) VALUES
('A102585', 'Tan Chee Chong', '288, Lorong Duta, Pupa Village', 'Selangor', 192587461, 2500, '15-
Jan-06', 'SE', 'Lecturer', 'M');
Book:
INSERT INTO `book` (`id`, `title`, `author`, `publisher`, `pubYear`, `isbnNo`) VALUES
(00001, 'An introduction to Database Systems', 'A.K. Allen', 'McGrawHill ', 2001, 74895841);
Borrow:
INSERT INTO `borrow` (`studentID`, `bookID`) VALUES ('10010', 0025);
Student:
INSERT INTO `student` (`id`, `sName`, `Address`, `state`, `contactNo`, `sex`, `email`) VALUES

11DATABASE MANAGEMENT SYSTEM
('S10010', 'Chan Wai Yee', '56A, Jalan 2/14,Taman Midah', 'Kuala Lumpur', '012-3256780', 'F',
'[e-mail]');
(c) Enhancement features of SQL integrity:
(i) alter table borrow add constraint index_book_id foreign key (bookID) references book(id)
ALTER TABLE borrow ADD CONSTRAINT unique_book UNIQUE (bookID);
(ii)ALTER TABLE staffADD CHECK (salary>0);
4. Relational Algebra Operation
(a) i. כ COUNT id (σ salary > 2000 (Staff))
ii. π id, SName, contactNo(Student)
iii. π book.title, book.publisher(Book) book.id = book.id(Borrow)

12DATABASE MANAGEMENT SYSTEM
(b) Output:
i.
Book id title author publish
er
pubYe
ar isbnNo
0026
7
Engineering
Mathematics
W.C
Evans Oxford 2005 447856
8
ii.
Book Title Author Publisher
An
Introduction
to
Database
Systems
A.K.
Allen McGrawHill
Introduction
to
Relational
Database
R.
Torlone
Addison
Wesley
Programming
with
Visual Basic
N.B.
Grag
Prentice
Hall
Mathematics
for
Engineering
T. Date Addison
Wesley
Fundamental
of
Programming
W.L.
Loure
Prentice
Hall
Engineering
Mathematics
W.C.
Evans Oxford
Engineering
Mathematics
D.
Woods
Prentice
Hall

13DATABASE MANAGEMENT SYSTEM
iii.
Result1 id sName address state contact
No sex email
S10020 Nichole
Tan
2-31-4,
Jalan
Mewah 6,
Taman
Merdeka
Penang 016-
6752856 F [e-mail]
Penang 017-
2548695 M [e-mail]
Penang 019-
2574165 F [e-mail]
Result2 id sName address state contact
No salary joinedDate dept positio
n
se
x
N574868
Winnie
Tan Sin
Yee
47-12,
Country
Heights,
Perdana
Villa
Penan
g
012-
6581262 2100 1-Nov-05 HR Secretar
y F
N778451
James
Toh
Heng
Wai
33,
Block
A, Jalan
Puteri 8
Penan
g 2000 20-Jun-05 ADM Secretar
y M
A254766
Yong
Ying
Xiang
33,
Block
A, Jalan
Puteri 8
Penan
g
013-
5541338 2800 1-Mar-06 SE Lecture
r F
A740026 Vijay
Lot 3,
Lebuh
Timur,
Kotamas
Penan
g
017-
2548695 1800 15-Mar-07 SE Tutor M
FinalResult sName
Nichole
Tan
Vijay

14DATABASE MANAGEMENT SYSTEM
Fong
Siow
Ting
Winnie
Tan Sin
Yee
James
Toh
Heng
Wai
Yong
Ying
Xiang
iv.
Borro
w
studentI
D
bookI
D
10010 00025
11094 00107
11094 00005
10121 00107
10020 00001
10020 00005
B bookI
D
00005
00107

15DATABASE MANAGEMENT SYSTEM
References
"Learning Views In SQL Server". 2018. C-Sharpcorner.Com.
http://www.c-sharpcorner.com/UploadFile/f0b2ed/views-in-sql-server/
Keith, Mike, Merrick Schincariol, and Massimo Nardone. "Introduction." In Pro JPA 2 in Java EE
8, pp. 1-24. Apress, Berkeley, CA, 2018.
McGinnes, Simon, and Evangelos Kapros. "Conceptual independence: A design principle for the
construction of adaptive information systems." Information Systems 47 (2015): 33-50.