Please Tick (√ ) for

Re-do Assignment
Campus NP
APU Foundation & Degree
Programmes

Coursework Submission and Feedback Form

Name Name of Group Members (If Applicable)
Student No. 3 Intake: NP1F1809IT Govinda Neupane (NP000307)
Module Code & Title CT042-3-1-IDB Lal Bahadur Budha (NP000312)
Assignment Title RiverView Property System Manisha Regmi (NP000313)
Name of Lecturer Mr. Ramesh Suwal
Date Due Student E-Mail: govinda.neupane@study.lbef.edu.np
I have read and understand the regulations on Plagiarism and Academic Dishonesty and declare that the work
submitted does not
breach those regulations. Signed:____________________ ,___________________ , __________________

You must hand in to the designed APU Administrator – ensure that you receive you receipt.

Received By Signature Date Time

Marginal
Criteria Weighting Fail Pass Credit Distinction
Fail

Additional Comments: (These may be listed below or attached)

Provisional Assessment Result: Grade Date Lecturer’s Initi al

Taking account of above factors, these overall provisional assessment of your work is:

Credi
Distinction t Pass
A+: 80- A: 75- B+: 70- C+: 60-
100% 79% 74% B: 65-69% 64% C: 55-59% C-: 50-54%
Marginal Fail Fail
F+: 30-
D: 40-49% 39% F: 20-29% F-: 0-19%
The comments and assessment result are subject to both internal and external moderation at the appropriate
Examination Board.
Consequently, they may not reflect your final grade. You may not appeal against these result on ground of
Academic Judgment.
 GROUP ASSIGNMENT
TECHNOLOGY PARK MALAYSIA
CT043-3-1-IDB
NP1F1809IT
HAND OUT DATE: 23rd April 2019
HAND IN DATE: 21st July 21, 2019
WEIGHTAGE: 50%

Submitted By: Submitted To:

Govinda Neupane (NP000307) Mr. Ramesh Suwal

Lal Bahadur Budha (NP000312) Lecturer

Manisha Regmi (NP000313)

________________________________________________________________________

INSTRUCTIONS TO CANDIDATES:

1 Submit your assignment at the administrative counter.

2 Students are advised to underpin their answers with the use of references (cited
using the Harvard Name System of Referencing).
3 Late submissions will be awarded zero (0) unless Extenuating Circumstances
(EC) are upheld.
4 Cases of plagiarism will be penalized.
5 The assignment should be bound in an appropriate style (comb bound or
stapled).
6 Where the assignment should be submitted in both hardcopy and softcopy, the
softcopy of the written assignment and source code (where appropriate) should
be on a CD in an envelope / CD cover and attached to the hardcopy.
7 You must obtain 50% overall to pass this module.
 Table of Contents
1. Database System ..................................................................................................... 1

1.1 Database management system (DBMS) ............................................................... 1

1.2 Advantages of DBMS .......................................................................................... 2

2. Business Rules ........................................................................................................ 3

3. Normalization ......................................................................................................... 4

4. Entity Relationship Modelling................................................................................ 7

5. Database Schema .................................................................................................... 9

6. Table schema ........................................................................................................ 10

7. Data Dictionary..................................................................................................... 16

8. Structured Query Language (SQL)....................................................................... 18

8.1 Data Definition Language (DDL) ...................................................................... 18

8.2 Data Manipulation Language (DML) ................................................................ 23

9. Conclusion ............................................................................................................ 27

10. References ......................................................................................................... 28

11. Appendix ........................................................................................................... 30

1. Database System

A database is the organized collection of persistent data that can be used by a

company application system and model organization. Basically, the database is a
computerized recording method that displays its compactness, less drudgery, and up-
to-date information. It provides centralized data control when certain application
systems of a company use operationally to stored data. (Hernandez, 2014) The main
aim of the database is the creation of accurate data representation, data relationships,
and data restrictions relevant to a company.
Data, measured in bits and bytes, are raw facts and figures used in the computer
system for input. Similarly, information is processed, and organized data measured in
meaningful units such as time, quantity, etc.

1.1 Database management system (DBMS)

A DBMS is a software system that allows users to build, maintain and monitor
databases and to define a collection of application program. It encourages ACID
properties (Atomicity, Consistency, Isolation, and Durability), avoids unauthorized
entry to the database, making it more consistent and accurate. DBMS includes all data
structures and conditions, performing activities such as deleting, inserting and
updating. DBMS, as query language is provided, stores data in such a manner that
data can be retrieved and manipulated easily. (Whatisdbms.com, 2019)

1
1.2 Advantages of DBMS
1. Reduction in data redundancy

Data redundancy means repeat or overflow of same information multiple times. In

computer data storage and database systems, data redundancy is a common
problem. (IT Definitions, 2019) Such redundancy can result in increasing of size of
the database unnecessarily, data inconsistency, decrement in the efficiency of
database and data corruption. By incorporating the documents, the database tries to
reduce redundancy so that several copies of the same information are not recorded.
2. Elimination of Data inconsistency

Data consistency means that for all instances of an application, data values are the
same. This information is a cohesive method at a moment, which means that during
processing or transferring, the information is not altered. (Reference, 2019) DBMS
promotes information consistency through information redundancy elimination or
control. If the object is only recorded once in the database, all feature updates is made
once, and the fresh value is accessible instantly in DBMS.
3. Improved data integrity

The integrity of the database concerns the quality and reliability of data. The overall

concept of data integrity is completeness, accuracy and consistency of data. This can

be demonstrated because there are no modifications between two or two variants of a

file, so the data will remain intact. Data integrity is generally implemented during the

implementation phase of the database by using ordinary procedures and laws. By

using several error monitoring and inspection methods, DBMS maintains data

integrity.

2
4. Improved security

Database security is a database protection against unauthorized users. The DBA can
define user names and passwords (enforced by DBMS) as well as ensure that proper
access procedures are followed to identify people who can use the database. Access to
data by authorized users may be limited for each type of data access in the database,
e.g. retrieving, inserting, modifying, deleting, updating, etc. (Singh, 2009)
Improved data sharing, efficient data access, increased productivity of application
development, improved data accessibility and responsiveness, improved data backup
and recovery, etc. are some additional benefits that the DBMS offers.

2. Business Rules

A Business rule is a specific directive which restricts or defines an enterprise activity.

These rules may apply to almost all aspects of a business, such as the protocols on the
supply chain, data management and relationships with customers. Business rules help
to create a more specific set of business or business process
parameters. (Techopedia.com, 2019)
Likewise, the River View Company also incorporates different business rules. These

rules are described below.

1. Each branch has allocated a manager and extra information about the manager is

also stored.

2. Up to 10 groups of staff are under the supervision of Supervisor.

3. Each branch offers different leasing facilities where an employee can manage up

to 100 clients at any time.

4. One must register first at a Riverview database before being called as a client.

5. The database mainly composed of two types of property owner I.e. private owner

and Business Owner.

6. A lease is made between the client and the property after the property is leased.

3
3. Normalization

Normalization is a method to develop a set of relationships with desired

characteristics in accordance with the company information demands. It is the process
of organization of information in the database. Normalization is used to minimize
redundancies in the database. It is also used to remove unwanted features, such as
insert, update and remove anomalies. Normalization breaks the large tables into
smaller tables and links using relationships. The main aim of the normalization is to
define an adequate set of relationships that fulfills an enterprise’s information
demands. (www.javatpoint.com, 2019) 2019].
The normalization method involves a sequence of rules to test individual relationships
to normalize a database to any extent. If a requirement is not met, the relationship
which breaches the requirement must be decomposed into relationships
which fulfil the standards individually. (Connolly and Begg, 2014). The normalization
process was continued till third normal form.
1NF

The table is developed in the first Normal form so that it can be readily expanded so
that information can be retrieved easily when necessary. The first normal form may
give the repeated values in each field but for each record, the values are atomic.
The Normalization process in the first Normal form follows following rules.
1. Each column in the table should be an atomic valued attribute.
2. Each value in a column should belong to the same domain.
3. No two field should have the same name. (Studytonight.com, 2019)

4
 1NF (First Normal Form)--Automic Values

Branch_ID NameOfBusiness
Branch_name TypeOfBusiness
Address Client ID
Phone1 ClientName
Phone2 email
Phone3 Telephone
EmployeeID Address
EmpName AffordableRent
Address JoinedDate
Designation PropertyNo
Salary PropertyType
Supervisor Address
Username NoOfRooms
Password MonthlyRent
OwnerId LeaseID
OwnerName PaymentMethod
OwnerType PaidAmount
Address DoubleDeposit
email LeaseDuration
Password StartDate
Telephone EndDate

2NF

Two conditions must be satisfied for the table to be in second normal form.
1. The entire table must have gone through first normal form.
2. The table should not have partial dependencies. This means each record should be
uniquely identified using primary key. (Studytonight.com, 2019)

5
 2NF(Second Normal Form)-Primary Key Identification

Table Name: Branch Table Name: Property

Branch_ID PropertyNo
Branch_name PropertyType
Address Address
Phone1 NoOfRooms
Phone2 MonthlyRent
Phone3 OwnerId
EmpID

Table Name: Employee

EmployeeID Table Name: Clients
EmpName Client ID
Address ClientName
Designation email
Salary Telephone
Username Address
Password AffordableRent
Branch_ID JoinedDate
Supervisor EmployeeID

Table Name: Owner

OwnerId Table Name: Lease
OwnerName LeaseID
OwnerType PaymentMethod
Address PaidAmount
email DoubleDeposit
Password LeaseDuration
Telephone StartDate
NameOfBusiness EndDate
TypeOfBusiness PropertyID
3NF
A table is said to be in the Third Normal Form when,
1. It is in the Second Normal form.
2. And, it doesn't have Transitive Dependency. Transitive dependencies, if present,
should be removed by breaking the table and functional dependencies is added to
it. (Studytonight.com, 2019)

6
 3NF (Third Normal form)-Transitional Dependencies

Table Name : Branch Table Name: Owner Table Name: Property

Branch_ID OwnerId PropertyNo
Branch_name OwnerType PropertyType
Address Address
PhoneNo NoOfRooms
MonthlyRent
Table Name: Employee Table Name: PrivateOwner OwnerId
EmployeeID OwnerId EmpID
EmpName OwnerName
Address Address Table Name: Client
Designation TelephoneNo Client ID
Salary ClientName
Branch_ID Table Name: BusinessOwner Email
Supervisor OwnerID Telephone
NameOfBusiness Address
TypeOfBusiness AffordableRent
Table Name: EmployeeLogin Address JoinedDate
EmployeeID Telephone EmployeeID
Username ContactName
Password
Table Name: Lease
Table Name: OwnerLogin LeaseID
OwnerId PaymentMethod
Email PaidAmount
Password DoubleDeposit
LeaseDuration
StartDate
EndDate
PropertyID

4. Entity Relationship Modelling

An Entity Relationship (ER) model is a high standard conceptual model which defines
information as entities, properties and their relationships. (S. Btoush and M. Hammad,
2015). The Entity Relationship diagram describes how each entity in the database is
related with each other. The information is shown in the ER model using symbols.
Rectangular box, Ovals and diamond are the symbols that gives information about
entities, attributes and relationship between entities respectively. The understanding
of relationships between entities can help us eliminate any ambiguity or unnecessary
business process. The ER diagram has been kept in the appendix section of this
assignment.

7
 Relationship between Entities

Relationship is how the data are shared between the entities. Basically, there are three
types of relationship in a database management system.
1. One to one relationship

One case of an entity is associated with only one entity in this kind of relationship.

2. One to many relationship

In such a relationship, an entity instance is associated to zero, one or many other entity

instances, but for a second entity, in the first table there is only one instance.

3. Many-to-many relationship

Zero, one or more instances of an entity relate to zero, one or many other entity cases in this

sort of connection, and vice versa.

8
5. Database Schema

The database system is that skeleton that reflects the logical perspective of the
database. It describes the way in which the information is organized and how their
relationships exist between them. Through this, all the constraints to be applied on the
data are formulated. It defines the entities present on the database and their
relationship.

9
6. Table schema

Fig – Branch table

Branch table describes all details about each branch including

their branch_id, branch_name, address (I.e. postal code, street, district, city, province)
and phone details. In this branch table, branch_id is defined as primary key with
integer data type and phone is declared as unique and composite key having its sub
attributes as phone1, phone2, phone3.

Fig – Employee table

10
Employee table explain details about employees working in a branch. This table has
details as empID, empname, address (i.e. postal code, street, district, city, and
province), designation, salary, branch_Id and supervisor. In this table, branch_id is
taken as foreign key with its references as branch table. Similarly, empID is declared
to be primary key with integer data type and field name supervisor is defined as
recursive key with its reference within the table employees. Also, here attribute
supervisor is said to be optional because all the data inserted in table employees will
be only applicable for employees working under supervisor, but supervisor will not
have any value to work with in.

Fig – Employee login

This table stores employee's login details. It acquires empID as foreign key having
references with table employees. Similarly, attributes as empID and username are
declared to be unique because one employee can have access to only one login.

11
 Fig – Client table
In this table, Client deliever their details to their assigned employees. Clients are
allowed to pin on their details as clientID, clientname, address (I.e. postal code, street,
district, city, province), email, telephone, affordable rent and empID. Here, clientID is
declared as primary key, email and telephone are said to be unique key. Similarly,
affordable rent is another attribute allowed to function under the check constraint
named as salary.

12
 Fig – Property table

This table explains detailed description about all kinds of properties shown to clients.
This table includes preserve information like propertyNo, address (I.e. postal code,
street, district, city, province), monthly rent, ownerID, employeeID.
Here, ownerID and employeeID are foreign keys having their referneces from owner
table and employee table. Similarly, employeeID is declared to be unique because a
set of property will be assigned to a single employee only. PropertyNO is said to be
primary key and a check constraint is also allowed on column monthly rent under
the constraint_name as rent.

Fig – Owner table

13
This table describes about type of owner we are dealing with (I.e. Private owner and
Business owner). In this table, ownerID is determined as primary key with integer
data type and owner type as unique key. Simialrly, owner type is also defined under
check constraint named as check_type to determine its belonging entity.

Fig – Private owner

This table stores the records of all the private owner. It has the unique ID number
called OwnerID which is linked with OwnerID of Owner Table.

Fig: Business Owner

14
The Business Owner table keeps the records of all the business company that owns
the property for rental. Business names, Addresses, contact name, and telephone
numbers are stored here.

Fig: Owner Login

Each owner in the database is given a unique ID and username and password. The
owner must login first to get access to the database. The username is unique for all the
users.

Fig: Lease

15
A lease is drawn out between the client and the property when the property is rented
out. Method of payment, indication of double payment, paid amount, client details
and lease duration are stored in this table.

7. Data Dictionary

The Data Dictionary consists of a series of files that contain information on database
metadata. The database stores recordings of other objects that may be data
ownership, data relationships with other objects, and other information in the
database. Due to its significance, only information administrators can interact with
it. (Techopedia.com, 2019)
Branch Table

Employee Table

Employee Login Table

16
Client Table

Property Table

Field Data Type Domain Description Type Of Constraint Link As PK

PropertyNo int - Property Number pk,not null -
PostalCode int Postal code of Branch Not Null -
Province varchar 20 Name Of Province Not Null -
District varchar 30 District Not Null -
City char 30 City Not Null -
street char 25 Street name Not Null -
EmpID int - Employee ID Not Null Employees(EmpID)
OwnerID int - Owner ID Not Null Owner(OwnerID)
MonthlyRent money - Monthly rent of the property Not Null, check -

Owner Table

Owner Login Table

Private Owner Table

17
BusinessOwner Table

Lease Table

8. Structured Query Language (SQL)

Structured Query Language (SQL) was first developed in the 1970s and is often used
by database administrators as well as developers who write data integration scripts
and information analysts who want to create and operate analytical
queries. (SearchSQLServer, 2019)
SQL comprises of Data Manipulation Language (DML) and Data Definition
Language (DDL). The DDL is for the creation, deletion and alteration of a table
structure and other database items. DML can be used in table or charts to insert,
recover and update information. (Darmawikarta and Setiadi, n.d.)

8.1 Data Definition Language (DDL)

DDL is a set of SQL commands which can create and manipulate database structures.
Using DDL statements, items such as indexes, triggers, table and views can be
created, changed and deleted. (Study.com, 2019)

18
CREATE statements

1. create database RiverView

2. create table Branch(Branch_ID int primary key,

Branch_name varchar(60) not null,

PostalCode char(5) not null,

Province varchar(20) not null,

District varchar(20) not null,

City varchar(30) not null,

street varchar(30) not null,

Phone1 varchar(13) not null unique,

Phone2 varchar(13) unique,

Phone3 varchar(13) unique,

constraint check_phone check(phone1!=phone2 or phone2!=phone3 or Phone3

!=phone1 ));

3. create table Employees (EmpID int not null primary key,

EmpName varchar(50) not null,

PostalCode char(5) not null,

Province varchar(20) not null,

District varchar(20) not null,

City varchar(30) not null,

street varchar(30) not null,

Designation varchar(30) not null,

salary money not null,

Branch_ID int not null,

Supervisor int foreign key references Employees(EmpID) ,

19
 foreign key(Branch_ID) references Branch(Branch_ID))

4. create table EmployeeLogin(EmpID int unique foreign key references Employ

ees(EmpID) not null,

username varchar(30) unique not null,

password varchar(30) not null)

5. create Table Owner(OwnerID int primary key,

OwnerType varchar(10) unique not null,

constraint check_type check (OwnerType in ('private','Business')))

6. create table OwnerLogin(OwnerID int unique foreign key references Owner(o

wnerID),

Email varchar(50) unique not null,

password varchar(30) not null)

7. create table PrivateOwner(OwnerID int unique foreign key

references owner(ownerID),

OwnerName varchar(50) not null,

PostalCode char(5) not null,

Province varchar(20) not null,

District varchar(20) not null,

City varchar(30) not null,

street varchar(30) not null,

Telephone varchar(13) unique)

20
8. create table BusinessOwner(OwnerID int unique foreign key references Owne

r(ownerID),

NameOfBusiness varchar(40) not null,

TypeOfBusiness varchar(30) not null,

PostalCode char(5) not null,

Province varchar(20) not null,

District varchar(20) not null,

City varchar(30) not null,

street varchar(30) not null,

Telephone varchar(13) unique,

contactName varchar(50) not null)

9. create table Client(ClientID int primary key,

CLientName varchar(50) unique not null,

Email varchar(50) unique not null,

Telephone varchar(13) unique not null,

PostalCode char(5) not null,

Province varchar(20) not null,

District varchar(20) not null,

City varchar(30) not null,

street varchar(30) not null,

AffordableRent money not null,

EmployeeID int foreign key references Employees(EmpID),

constraint check_salary check(AffordableRent>0));

21
 10. create table Property(PropertyNo int primary key,

PostalCode char(5) not null,

Province varchar(20) not null,

District varchar(20) not null,

City varchar(30) not null,

street varchar(30) not null,

MonthlyRent money not null,

OwnerID int references Owner(OwnerID),

constraint check_rent check(MonthlyRent>0));

11. create table Lease(LeaseID int primary key,

PaymentMethod varchar(30) not null,

PaidAmount money not null,

DoubleDeposit char(3),

LeaseDuration int not null,

StartDate date not null,

Enddate date not null,

PropertyNo int foreign key references Property(PropertyNo),

constraint check_double check(DoubleDeposit in ('Yes','No')))

ALTER Statements

1. alter table Property add EmployeeID int unique not null

2. alter table Property add constraint Property_Handler foreign key(EmployeeID)

references Employees(EmpID)

DROP Statements

1. drop table EmployeeLogin

22
 2. drop table Employees

3. drop table Branch

8.2 Data Manipulation Language (DML)

Data Manipulation Language (DML) in SQL is a language that helps users to access
and manipulate data. Data Manipulation is an insertion, retrieval, deletion and
modification of information from the database. The objective of this statements is to
attain effective interaction with the system. It is similar to English language used for
retrieving and manipulating data. The basic DDL statements are SELECT, INSERT,
UPDATE and DELETE. (MBA Skool-Study.Learn.Share, 2019)
INSERT Statement

1. INSERT into Branch values (1001, 'Kathmandu

Branch', '44600','Three','Kathmandu','Maitidevi','Chiuree

Galli','014468546','014254653',NULL)

2. INSERT INTO Employees values

(101, 'Ramesh Suwal', '44560', 'Gandaki', 'Rupandehi', 'Butwal',

'Chaudaha', 'Manager', 450000, 1001, NULL)

3. INSERT INTO EmployeeLogin values(101,'Ramesh@123','@ramesh#12')

4. INSERT INTO Owner values (4001,'Private'),(4002,'Business');

5. INSERT INTO OwnerLogin values

(4001, 'govinda.neupane@study.lbef.edu.np','govinda@123'),

(4002, 'Kathmandu@rentcompany.org','rent@kathmandu2019');

6. INSERT INTO PrivateOwner values(4001,'Govinda

Neupane', '44600', 'Gandaki', 'Chitwan', 'Bharatpur', 'Bishal street', '9619287903');

7. INSERT INTO BusinessOwner values(4002,'Kathmandu

Rental Office','Service Business','44600','Province

3','Kathmandu','Maitidevi','Chiuree Galli','014464985','Manisha Regmi');

23
 8. INSERT INTO Client Values(2001,'Lal

Bahadur Budha', 'lal.bahadur.budha@study.lbef.edu.np', '9849020083', '44650','Ka

rnali', 'Jumla', 'Singati', 'balli Galli', 6500, 101);

9. INSERT INTO Property values (2019, '45600', 'Province

5', 'Kaski', 'Pokhara', 'Prithvi Chowk', 8500, 4001, 101);

10. INSERT INTO Lease values (3001, 'Cheque', 17000, 'yes', 3, '2019-3-

12', '2021-3-12', 2019);

SELECT Statement

SELECT * FROM Employees WHERE EmpID=101

UPDATE Statement

UPDATE OwnerLogin set password='@govinda123' where ownerID=4001;

DELETE Statement

DELETE FROM ownerLogin where ownerID=4001

24
1. List the details of branches in a given state

SELECT * FROM Branch where Province='Johor'

2. List the name, position, and salary of employees at a given branch (e.g., BR012),
ordered by employee name.

SELECT Branch.Branch_ID, Branch.Branch_Name,Branch.PostalCode,

Branch.Province,Branch.District,Branch.City,
Branch.Street, Employees.EmpName AS Manager FROM
Branch inner join Employees on Branch.Branch_ID=Employees.Branch_ID
WHERE Employees.Designation='Manager' and Branch.Branch_ID='BR012'
order by Employees.EmpName

3. Identify the total number of employees and the sum of their salaries.

SELECT COUNT(EmpID) as TotalEmployees, SUM(salary) as TotalSalary

FROM Employees

4. List the name of each Manager at each branch, ordered by branch address.

SELECT Branch.Branch_name, Employees.EmpName As Manager FROM

Branch inner join Employees ON Branch.Branch_ID=Employees.Branch_ID
WHERE Employees.Designation='Manager' order by Employees.Province asc

5. List the details of properties for rent managed by a named employee (e.g.,
Alexander).

SELECT Property.PropertyNo, Property.PostalCode, Property.Province,

Property.District, Property.City, Property.street, Employees.EmpName as
PropertyManager
FROM Property inner join Employees ON Property.EmployeeID =
Employees.EmpID where
Employees.EmpName = 'Alaxander';

25
6. Identify the total number of properties of each type at all branches.

SELECT Property.PropertyType, COUNT(Property.PropertyType) FROM

Property GROUP BY PropertyType

7. List the number, name, and telephone number of clients and their property
preferences at a given branch (e.g., BR012).
SELECT Client.ClientID, Client.CLientName, Client.Telephone,
Property.*
FROM Property inner join Client on Property.PropertyNo =
Client.PropertyNo
inner join Employees on Client.EmployeeID = Employees.EmpID
WHERE Employees.Branch_ID = 'BR012'

8. List the total number of leases with rental periods that are less than one year at all
branches.

SELECT Count(Lease.LeaseID)as LeaseLessThanAYear from Lease where

LeaseDuration<1;
9. List the details of leases due to expire next month at a given branch (e.g., BR012).

SELECT * FROM Lease inner join Property

on Lease.PropertyNo = Property.PropertyNo
inner join Employees on Property.EmployeeID = Employees.EmpID
WHERE Employees.Branch_ID = 1001 and DATEDIFF (MM,
GETDATE(), Lease.Enddate) = 1
10. List the property number, address, type, and rent of all properties in Johor, ordered
by rental amount.
SELECT Property.PropertyNo, Property.PostalCode,Property.Province,
Property.District,Property.City,Property.street,Property.MonthlyRent
FROM Property inner join Employees on
Property.EmployeeID = Employees.EmpID
inner join Branch on Employees.Branch_ID=Branch.Branch_ID
WHERE Branch.Branch_name = 'Johor'

26
9. Conclusion

A database has been created that can be used by the RiverView Company for rental
purposes. A total of 10 tables were developed to store information about branches,
employees, login details, the property and its owner, customers and lease details. Each
table in the database is directly or indirectly associated with another table. This
project has provided us with fundamental knowledge of database design and the
opportunity to apply innovative skills. We have gained an understanding of the real-
time implementation of the database design.
This system makes interacting with the database easy for the operator and handling of
the database data. The operator can easily add, remove and update records in the
database.

27
10. References

1. Hernandez, M. (2014). Database Design: For mere Mortals. 3rd ed. Michigan:
Edwards Brothers Malloy, p.4.
2. Whatisdbms.com. (2019). Database Management System. [online] Available at:
https://whatisdbms.com/characteristics-of-database-management-system/
[Accessed 12 May 2019].
3. IT Definitions. (2019). Data Redundancy definition and information. [online]
Available at: https://www.defit.org/data-redundancy/ [Accessed 12 May 2019].
4. Reference. (2019). What Is Data Consistency? [online] Available at:
https://www.reference.com/technology/data-consistency-58b028890c673b3c
[Accessed 12 May 2019].
5. Singh, S. (2009). Database Systems. 3rd ed. E Rutherford: Prentice Hall
[Imprint], pp.32-33.
6. Techopedia.com. (2019). What is a Business Rule? - Definition from
Techopedia. [online] Available at:
https://www.techopedia.com/definition/28018/business-rule [Accessed 14 May
2019].
7. www.javatpoint.com. (2019). DBMS Normalization - javatpoint. [online]
Available at: https://www.javatpoint.com/dbms-normalization [Accessed 30
Jun].
8. Connolly, T. and Begg, C. (2014). Database Systems: A Practical Approach to
Design, Implementation, and Management: Global Edition. 6th ed. Harlow:
Pearson Education.
9. Studytonight.com. (2019). First Normal Form (1NF) of Database Normalization
| Studytonight. [online] Available at: https://www.studytonight.com/dbms/first-
normal-form.php [Accessed 4 Jul. 2019].
10. Studytonight.com. (2019). Second Normal Form (2NF) of Database
Normalization | Studytonight. [online] Available at:
https://www.studytonight.com/dbms/second-normal-form.php [Accessed 4 Jul.
2019].

28
11. Studytonight.com. (2019). Third Normal Form (3NF) of Database Normalization
| Studytonight. [online] Available at: https://www.studytonight.com/dbms/third-
normal-form.php [Accessed 4 Jul. 2019].
12. S. Btoush, E. and M. Hammad, M. (2015). Generating ER Diagrams from
Requirement Specifications Based on Natural Language Processing. International
Journal of Database Theory and Application, 8(2), pp.61-70.
13. Techopedia.com. (2019). What is a Data Dictionary? - Definition from
Techopedia. [online] Available at:
https://www.techopedia.com/definition/27752/data-dictionary [Accessed 7 Jul.
2019].
14. SearchSQLServer. (2019). what is SQL (Structured Query Language)? -
Definition from WhatIs.com. [online] Available at:
https://searchsqlserver.techtarget.com/definition/SQL [Accessed 9 Jul. 2019].
15. Darmawikarta, D. and Setiadi, M. (n.d.). SQL for MySQL.
16. Study.com. (2019). Data Definition Language (DDL): Definition & Example -
Video & Lesson Transcript | Study.com. [online] Available at:
https://study.com/academy/lesson/data-definition-language-ddl-definition-
example.html [Accessed 9 Jul. 2019].
17. MBA Skool-Study.Learn.Share. (2019). Data Manipulation Language (DML)
Definition | IT & Systems Dictionary | MBA Skool-Study.Learn.Share.. [online]
Available at: https://www.mbaskool.com/business-concepts/it-and-
systems/13118-data-manipulation-language-dml.html [Accessed 9 Jul. 2019].

29
11. Appendix

30
Work Load Matrix

Roll No Name Contribution Signature

NP000307 Govinda Neupane 33%

NP000312 Lal Bahadur Budha 33%

NP000313 Manisha Regmi 34%

31
 CT042-3.1-IDB
Assignment Marking Scheme

Group No:3 Intake: NP1F1809IT

Group Component
Total: _______ /70

PART A: Group Marks

FAIL MARGINAL FAIL PASS CREDIT DISTINCTION
(40%)
No/little evidence of Major missing components, Average documentation Very minimal errors in Quality document with no
documentation / hardcopy poor document content and with logical errors. formats and standard of errors in formats and standard
Documentation submitted standards. Incomplete Irregular standards documentation. All content of documentation. All content
(10 marks) deliverables. applied. Partially missing fully documented according fully documented according to
contents to requirements, minor requirements, well referenced.
issues on references.
Marks 0 1 2 3 4 5 6 7 8 9 10

Poor understanding of the Some understanding of the Sound understanding of Good understanding of the Excellent understanding of the
Business Rule business rules. Irrelevance business rules. Some the business rules. Most business rules. Relevant business rules. All business
(10 marks) business rules with business rules do not match business rules match the business rules with rules fully comply with the
requirements. the requirements. requirements. requirements. requirements.

Marks 0 1 2 3 4 5 6 7 8 9 10
No/little evidence in Database implemented but Database implemented Database implemented with Database implemented with
documentation or contains no integrity with minimal integrity appropriate integrity almost comprehensive integrity
Database schema and tables
softcopy. constraints and/or contains constraints. Justifications constraints but with some constraints and good
implemented
major errors. No given but poor. minor errors and/or justifications provided. Well-
(15 marks)
justifications given. Schema Referential Integrity has omissions. ERD proposed structured table implementation
not documented. error, tables partially matches the table reflecting ERD.
 matches ERD proposed implemented.
Marks 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
No evidence in report or ERD missing major ERD contains some errors ERD has minor errors. ERD contains hardly any errors.
softcopy/ constraints & attributes. and/or omissions of Assumptions logical but has Assumptions logical and
Incomplete ERD / Assumptions do not reflect attributes & constraints. limitations on relationship. comprehensive.
ER Modelling individual relation the scenario as a complete Assumptions acceptable Good analysis on Excellent business rule
(10 marks) missing system. with limitation. functionalities & constraints. statement. Demonstrated
Business rules missing or Major limitation on business Minimal business rules Adequate assumptions. critical analysis
irrelevant to case study rules / incorrect analysis on stated with missing Minor error found
and database design case study. Incomplete constraints and important
proposed business rules analysis.
Marks 0 1 2 3 4 5 6 7 8 9 10
No/little evidence in Mapping and normalization Minor errors in mapping. Mapping done correctly but No errors in the mapping and
documentation or contain many errors and/or Most relations will be in there may be some minor normalization carried out
Normalization and logical softcopy. omissions. Does not reflect 3NF but there may be errors and/or omissions in correctly with appropriate
mapping Student did not attempt the group relational model some normalization the normalization explanations.
(15 marks) normalization. issues. Integration of illustration.
system relational model
has limitation
Marks 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
No evidence of data Contains minimal Moderate representation Good description of entities Excellent encode of entities
Data Dictionary dictionary / entities description of the database with some elements with some explanation on with relevant features,
(10 marks) missing / no features and the entities. Hardly any missing in features. constraints, attributes and constraints, attributes and
described by the data constraints found attribute values attribute values
dictionary
Marks 0 1 2 3 4 5 6 7 8 9 10

Comments: _______________________________________________________________________________________________
Student Name & ID : Govinda Neupane (NP000307) Individual Score:_____________________
Individual Component
Total : _______ /30

PART B: Individual
FAIL MARGINAL FAIL PASS CREDIT DISTINCTION
Marks (60%)
Demonstrates poor Demonstrates limited Demonstrates adequate Demonstrates good Demonstrates excellent
Understanding & understanding of problem. understanding of problem. understanding of the problem. understanding of the understanding of the
Problem Analysis Explanations will be limited Explanations will be limited Some explanations given but problem. Satisfactory problem. Good explanations
(10 marks) and likely to be insufficient to but sufficient to demonstrate likely to be insufficient to explanations given and will given and will be able to
demonstrate that the student that the student has some demonstrate that the student be able to demonstrate that demonstrate in-depth
understands the work. very basic understanding of understands to a reasonable the student understands to a understanding of the work
the problem. depth. reasonable depth.
Marks 0 1 2 3 4 5 6 7 8 9 10
SQL - Data Definition DDL not attempted. DDL attempted but does not Table definition created with Table definition found with Excellent data definition
Language ** Or no participation function correctly. minimal settings. Constraints appropriate constraints and with appropriate constraints,
(10 marks) evidence in table on attributes partially / not complete attributes. Data data types, formats and
development implemented types & formats not well used integrity rules applied
Marks 0 1 2 3 4 5 6 7 8 9 10
DML not attempted. DML Query attempted but Query functions correctly. Query functions correctly. Query functions correctly.
SQL - Data
** Or no participation does not function correctly. Allows searching by single Allows searching by multiple Allows searching by
Manipulation Language
evidence in web query field only. Search results fields & tables. Search results multiple fields & tables.
(10 marks)
development poorly displayed. not-well displayed. Search results well
displayed.
Marks 0 1 2 3 4 5 6 7 8 9 10

Comments: _______________________________________________________________________________________________
Student Name & ID: Lal Bahadur Budha (NP000312) Individual Score:_____________________
Individual Component
Total : _______ /30

PART B: Individual
FAIL MARGINAL FAIL PASS CREDIT DISTINCTION
Marks (60%)
Demonstrates poor Demonstrates limited Demonstrates adequate Demonstrates good Demonstrates excellent
Understanding & understanding of problem. understanding of problem. understanding of the problem. understanding of the understanding of the
Problem Analysis Explanations will be limited Explanations will be limited Some explanations given but problem. Satisfactory problem. Good explanations
(10 marks) and likely to be insufficient to but sufficient to demonstrate likely to be insufficient to explanations given and will given and will be able to
demonstrate that the student that the student has some demonstrate that the student be able to demonstrate that demonstrate in-depth
understands the work. very basic understanding of understands to a reasonable the student understands to a understanding of the work
the problem. depth. reasonable depth.
Marks 0 1 2 3 4 5 6 7 8 9 10
SQL - Data Definition DDL not attempted. DDL attempted but does not Table definition created with Table definition found with Excellent data definition
Language ** Or no participation function correctly. minimal settings. Constraints appropriate constraints and with appropriate constraints,
(10 marks) evidence in table on attributes partially / not complete attributes. Data data types, formats and
development implemented types & formats not well used integrity rules applied
Marks 0 1 2 3 4 5 6 7 8 9 10
DML not attempted. DML Query attempted but Query functions correctly. Query functions correctly. Query functions correctly.
SQL - Data
** Or no participation does not function correctly. Allows searching by single Allows searching by multiple Allows searching by
Manipulation Language
evidence in web query field only. Search results fields & tables. Search results multiple fields & tables.
(10 marks)
development poorly displayed. not-well displayed. Search results well
displayed.
Marks 0 1 2 3 4 5 6 7 8 9 10

Comments: _______________________________________________________________________________________________
Student Name & ID: Manisha Regmi Individual Score:_____________________
Individual Component
Total: _______ /30

PART B: Individual
FAIL MARGINAL FAIL PASS CREDIT DISTINCTION
Marks (60%)
Demonstrates poor Demonstrates limited Demonstrates adequate Demonstrates good Demonstrates excellent
Understanding & understanding of problem. understanding of problem. understanding of the problem. understanding of the understanding of the
Problem Analysis Explanations will be limited Explanations will be limited Some explanations given but problem. Satisfactory problem. Good explanations
(10 marks) and likely to be insufficient to but sufficient to demonstrate likely to be insufficient to explanations given and will given and will be able to
demonstrate that the student that the student has some demonstrate that the student be able to demonstrate that demonstrate in-depth
understands the work. very basic understanding of understands to a reasonable the student understands to a understanding of the work
the problem. depth. reasonable depth.
Marks 0 1 2 3 4 5 6 7 8 9 10
SQL - Data Definition DDL not attempted. DDL attempted but does not Table definition created with Table definition found with Excellent data definition
Language ** Or no participation function correctly. minimal settings. Constraints appropriate constraints and with appropriate constraints,
(10 marks) evidence in table on attributes partially / not complete attributes. Data data types, formats and
development implemented types & formats not well used integrity rules applied
Marks 0 1 2 3 4 5 6 7 8 9 10
DML not attempted. DML Query attempted but Query functions correctly. Query functions correctly. Query functions correctly.
SQL - Data
** Or no participation does not function correctly. Allows searching by single Allows searching by multiple Allows searching by
Manipulation Language
evidence in web query field only. Search results fields & tables. Search results multiple fields & tables.
(10 marks)
development poorly displayed. not-well displayed. Search results well
displayed.
Marks 0 1 2 3 4 5 6 7 8 9 10

Comments: _______________________________________________________________________________________________