Conceptual and Logical Model for Blue Mountains College Database

This assignment presents a comprehensive database project centered around the Blue Mountains College, detailing the design of a relational database to manage the college's information. The solution begins with a conceptual model, outlining entities such as Courses, Students, Units, Classes, Rooms, Buildings, and StaffMembers, along with their relationships and cardinalities. The model incorporates super-types and sub-types, specifically using hierarchical inheritance to represent Face to Face and Online classes. The logical model defines data attributes, data types, and primary and foreign keys for each table, including Courses, Students, Units, StudentUnits, Classes, Rooms, Buildings, and StaffMembers. The relationships between entities are clearly defined, including one-to-one and one-to-many relationships. The document concludes with a bibliography of relevant database systems and design literature.

Running head: THE BLUE MOUNTAINS COLLEGE DATABASE
The Blue Mountains College Database
Name of the Student
Name of the University
Author’s note:

1THE BLUE MOUNTAINS COLLEGE DATABASE
Table of Contents
Conceptual Model:...........................................................................................................................2
Logical Model:.................................................................................................................................5
Bibliography:.................................................................................................................................11

2THE BLUE MOUNTAINS COLLEGE DATABASE
Conceptual Model:
Entities: The entities are as following
i. Courses
ii. FormOfEducation
iii. Students
iv. Units
v. Classes
vi. Rooms
vii. Buildings
viii. StaffMembers
Entity Super and Sub Types: The proposed database of Blue Mountains college has
classes as the super type and face to face and online classes as sub types. Super type is a special
entity that has relation with one or more subtype entities as well as it contain the attributes and
method that are common to its subtypes. The subtypes are referred to as the subgroups of
subtype entities and these subtypes has unique properties that make then differ from one another.
Each class will be either face to face or online. Both of these sub types entities will inherit the
attributes and methods of super type, classes class. The super-types and subtypes are parent and
child entities respectively. Inheritance is the technique in object oriented programming where
one or more classes acquire the property of another class. The class that contain the common
properties are called parent class. Additionally, the class that inherit the properties are called
child class. In the concept of database, there are no concept of classes but it has entities.
Inheritance can be of several types such as single, multiple, multilevel and hierarchical

3THE BLUE MOUNTAINS COLLEGE DATABASE
inheritance. In the proposed Blue Mountains College database the hierarchical inheritance can be
seen. In hierarchical inheritance, many child entities inherit the properties of parent entities. In
this database model face to face and online child entities inherits the properties of ‘classes’
entity.
Relationships and Cardinalities: Each staff member or lecturer will be assigned to each
class and classes must have lecturers. This way the optionality between staff member and class
entities become 1 and 1. As each staff member will assigned to one class and each class has only
one lecturer. This is why the cardinality between staff member and classes has one to one
cardinality. Each room is in a building. A building must have a room and a room must be in a
building. The optionality is 1 and 1. One building can have multiple rooms but a room is in one
building. Building and room entities has one to many cardinality. A room is assigned to a single
class and each class must have a single room. Therefore, the optionality and cardinality between
room and classes are 1-1 and one to one respectively. One student may not choose a unit while
registering to Blue Mountains College. The unit may not have any student at creation time. That
is why the student and unit entities has 0-0 optionality among them. One student can take
multiple units and a single unit has multiple students. Therefore, student and unit entities has
many to many cardinality among them. For each course, Blue Mountains College has several
units. It is not mandatory that a course must have a unit but a unit must be under a course. That is
course and unit entities has one to many cardinality and 0-1 optionality. A class is associated
with the currently taught unit.
Primary Keys:
i. Primary key of courses entity is course id
ii. Primary key of students entity is student id

4THE BLUE MOUNTAINS COLLEGE DATABASE
iii. Primary key of units entity is unit id
iv. Primary key of classes entity is class id
v. Primary key of rooms entity is room id
vi. Primary key of buildings entity is building id
vii. Primary key of staff member entity is member id
Conceptual Model:
Figure 1: Conceptual Model

5THE BLUE MOUNTAINS COLLEGE DATABASE
(Source: Created by Author)
The proposed conceptual model represent the entities and the relationships among the
entities. Each entity in the database represents a specific role or element within the business. The
relationships perfectly represents the business rules of Blue Mountains College.
Logical Model:
Data Attributes and Types:
Table: Courses
Attribute Name Data Type Range
courseID Integer 11
courseTitle Varchar 250
courseDescription Text None
Table: Students
Attribute Name Data Type Range
studentID Integer 11
name Varchar 250
address Text None
educationForm Varchar 250
Table: Units
Attribute Name Data Type Range

6THE BLUE MOUNTAINS COLLEGE DATABASE
unitID Integer 11
unitTitle Varchar 250
unitDescription Text None
Table: StudentUnits
Attribute Name Data Type Range
unitID Integer 11
studentID Integer 11
unitScore Decimal 5,2
Table: Classes
Attribute Name Data Type Range
classID Integer 11
classTime Time None
classType Varchar 250
Table: Rooms
Attribute Name Data Type Range
roomID Integer 11
capacity Integer 11
Table: Buildings

7THE BLUE MOUNTAINS COLLEGE DATABASE
Attribute Name Data Type Range
buildingID Integer 11
location Text None
Table: StaffMembers
Attribute Name Data Type Range
memberID Integer 11
subject Varchar 250
Foreign Keys: The courseID primary key will be used as a reference in ‘units’ entity.
Therefore, courseID attribute in ‘units’ entity is the foreign key with reference to courseID
primary key of ‘courses’ entity. In order to decompose the many to many into many to one and
one to many, a bridge table called studentUnits has bee created. This table will use the studentID
and unitID as primary key and these two attributes are foreign keys. The non-key attribute of this
table is unitScore. The class table will be have a unitID that is used as a foreign key. It refers to
the primary key of units entity. The memberIDwill be used as a reference attribute which refers
to memberID of StaffMembers entity.
Table: Courses
Attribute Name Key Reference
courseID Primary key None
courseTitle None None

8THE BLUE MOUNTAINS COLLEGE DATABASE
courseDescription None None
Table: Students
Attribute Name Key Reference
studentID Primary Key None
name None None
address None None
educationForm None None
Table: Units
Attribute Name Key Reference
unitID Primary key None
unitTitle None None
unitDescription None None
courseID Foreign key Courses
Table: StudentUnits
Attribute Name Key Reference
unitID Primary key, Foreign Key Units
studentID Primary key, Foreign Key Students
unitScore None None

9THE BLUE MOUNTAINS COLLEGE DATABASE
Table: Classes
Attribute Name Key Reference
classID Primary key None
classTime None None
classType None None
memberID Foreign Key StaffMembers
unitID Foreign Key Units
Table: Rooms
Attribute Name Key Reference
roomID Primary key None
capacity None None
buildingID Foreign Key Buildings
Table: Buildings
Attribute Name Key Reference
buildingID Integer 11
location Text None
Table: StaffMembers
Attribute Name Key Reference
memberID Integer 11

10THE BLUE MOUNTAINS COLLEGE DATABASE
subject Varchar 250
One-to-One Relationship: As each staff member will assigned to one class and each
class has only one lecturer. This is why the cardinality between staff member and classes has one
to one cardinality.
Logical Model:
Figure 2: Logical Model
(Source: Created by Author)

11THE BLUE MOUNTAINS COLLEGE DATABASE
Bibliography:
Al-Othman, A., 2018. Database Systems.
Hingorani, K., Gittens, D. and Edwards, N., 2017. REINFORCING DATABASE CONCEPTS
BY USING ENTITY RELATIONSHIPS DIAGRAMS (ERD) AND NORMALIZATION
TOGETHER FOR DESIGNING ROBUST DATABASES. Issues in Information Systems,
18(1), pp.148-155.
Kruse, S.L.K. and Wells, M.G., 2016. Optionality of ERD Relationships: Project for the
Introduction to Database Course. In Proceedings of the EDSIG Conference ISSN (Vol. 2473, p.
3857).
Shoval, P., 2018. A Method for Modeling a Schema for Graph Databases. Digital Presentation
and Preservation of Cultural and Scientific Heritage, (VIII), pp.99-104.
Van Aken, D., Pavlo, A., Gordon, G.J. and Zhang, B., 2017, May. Automatic database
management system tuning through large-scale machine learning. In Proceedings of the 2017
ACM International Conference on Management of Data (pp. 1009-1024). ACM.