COMP1140-Assignment 3 Solved

Data Requirements:

Catalogue

The School of SEEC maintains information about its physical and online resources. SEEC have physical resources which students can borrow/loan such as cameras, speakers, phones and CDs. SEEC also have online resources which can be used such as various software that students can have access to if required. 

Collections

The School have many collections of items in each respective location. A collection contains a unique name – “ES105 – Speaker”, etc. and provides the location where the collection exists physically in the building. Each physical item belongs to a collection within the School of SEEC. Likewise, the School of SEEC also provides information as to where to access and download software for student use. Each software item is a part of a collection which exists virtually and is arranged into different categories depending on the course (e.g. Engineering, Computing, etc.) that the software is related to. 

Resources

The School of SEEC have many resources that they can loan students during the duration of their degree to assist them in their study or to use during their assignments. Every resource has: 

·       a resource ID (which is unique)

·       a description of the item

·       a status (e.g. ‘in use’, ‘maintenance’, ‘available’, ‘borrowed’, ‘lost’, ‘damaged’, etc.)

There are two types of resources students can use; moveable and non-moveable. Moveable resources include items such as cameras, microphones, speakers, etc. Moveable resources have a:

·       name

·       make

·       manufacturer

·       model

·       year

·       asset value

For example, the SEEC resource database about would maintain information about cameras. Information on cameras maintained by the School include:

·       Brand of camera (Canon, Nikon, Sony)

·       Type of camera (camcorder, DSLR, action)

·       Camera lenses (standard/general purpose, wide angle) 

·       Charge time and battery life 

·       Colour 

·       Model Number

·       Serial Number

·       Storage location of the cameras depending on what is loaned/used more. 

Non-movable resources are resources such as classrooms, laboratories, etc. Each immovable resource has:

·       maximum person capacity 

·       see location entity below for further explanation

 

 

Each resource is placed into a category, such as ALL cameras, ALL speakers, etc. Each category has a:

·       unique code

·       name

·       description

·       max time allowed to borrow/book (in days and/or hours)

Location

All resources have a location where they are situated that can accessed by staff members and student members alike. These locations have a:

·       unique ID

·       room

·       building

·       camp

Loan

Lending and reservation rights are offered to students by staff who are enrolled in courses offered by SEEC. These students and staff are known as “members” in the system. Members have a:

·       unique ID (student or staff number)

·       name

·       address

·       phone number

·       email

·       status (‘active’, ‘disabled’)

·       comments field

Student members enrol in courses offered by SEEC. Course information about 
s and student enrolments are maintained. A course offering has a:

·       offering ID (which is unique)

·       course ID (which is unique)

·       name

·       semester offered

·       year offered

·       course beginning/end date

The loaning of an item is an entity in itself. This differs from reservation as the act of loaning a resource has different requirements and attributes. Each loan has a:

·       member ID

·       date loaned

·       time loaned

·       resource ID

·       date due

·       time due

Privilege

Staff members can borrow/reserve resources. Staff have no limits on the number of resources that staff can use. Students are granted privileges on what they can borrow/reserve depending on the course they are enrolled in. Courses are assigned privileges to different categories of resources. Each privilege has a:

·       name

·       description

·       a privilege category

·       maximum number of resources that a student member can borrow/book at any given time

A member can loan moveable resources (such as cameras, speakers, etc.) if their privileges allow it. Information about the resource when it is loaned is maintained for the duration of it loan. 

 

New Acquisitions

New acquisitions can be made to the School by a student or staff member. An acquisition contains:

·       a person requesting acquisition

·       an item name

·       make

·       manufacturer 

·       model

·       year

·       a description of the required item

·       its urgency

The administrator of the system assigns values to the acquitted item such as:

·       admin ID

·       status (‘acquired’, ‘pending’, etc.)

·       a fund code

·       vendor code

·       price

·       notes

Reservations

Resources can be reserved by members as long as their privileges allow them to borrow/book that resource. The resource will be booked for pickup/use by the student or staff member at the requested date and time after a reservation is made. Reservations have the date and time the item is required and a due date and time. No two reservations should ever conflict as that becomes problematic for one of the members attempting to borrow/book the resource. 

Transaction Requirements:

Data Manipulation Operations

-        Insert/update/delete an item in the database 

-        Insert/update/delete a copy of item in the database

-        Insert/update/delete the status of an item

-        Insert/update/delete an online information source 

-        Insert/update/delete staff of the School of SEEC

-        Insert/update/delete location of a resource

-        Insert/update/delete members

-        Insert/update/delete member information

-        Insert/update/delete acquisition items

-        Insert/update/delete reservations

Queries

-        Search for an item based on brand, title, serial number, keyword and/or publisher

-        List current loans by a specific member

-        List frequently loaned items for each semester

-        List members who have loaned an item over a duration of time

-        List new acquisitions made by staff or student members 

-        Fines report containing information about fines imposed and members fined

-        Provide information about student member privileges

-        Provide information about reservation dates of resources

-        Provide information about the privilege’s members have

-        Provide information about the maximum possible loans a student member can take out

Business Rules:

1.     Expiration of Student Member Access

·       A student’s borrowing privileges are taken away when they finish their enrolled course. 

·       When the date is later than the end date, they are automatically taken away.

·       The status of the student member is set to ‘disabled’

2.     Maximum number of items loaned or reserved at any one time

·       A student member cannot borrow, or reserve more than the maximum number of items specified in his/her privileges at any given time

·       Staff members have precedence over student members regarding loaning resources

3.     Late returns by student member penalties

·       Each student member has a default set of points earned at the beginning of their course (12 to start with)

·       For each incurred day that the item is overdue, a penalty of 3 points is given, which is deducted from the total amount that student member has

·       When the points come to 0, member status of that student is disabled, and borrowing/reservation privileges are revoked

4.     Cancellation of Reservations

·       A reserved item is cancelled if it is not collected by the member on the day of the required due date

·       Non-cancellation of a reservation by a member means 1 point is deducted from the member’s total

·       Administrators can also cancel any reservation as they hold the right to do so

5.     Borrowing/Reservation Periods

·       The duration of the borrowing/reservation periods, being either number of days or hours, is determined by the category to which the item belongs


 

ENTITY TYPES:

Entity Name
Description
Aliases
Occurrence
Catalogue
A database of resources that is used to search for loanable items
Database
Online database where the catalogue can be accessed
Resources
Items both physical and virtual that are used by students to aide their study
Borrowed Items
Physically stored in certain locations

Virtually stored online 
Category
Describes a group of items which are similar/the same
 
Items are categorized so searching for them and loaning is more efficient
Location
Place where resources are kept
 
The place, physically or virtually, where a resource is stored
Movable Resource
Resources such as cameras, speakers, etc.
 
Loaned out by members of SEEC, either students or staff
Non-movable Resource
Resources such as classrooms, studios, laboratories, etc.
Buildings/Rooms
Loaned out by members of SEEC, either students or staff
Loan
Borrowing an item/resource
Borrow
Items that are borrowed from SEEC
Course Offering
A course that is offered by the University
Class/Subject
Members can partake in a course that is offered
Reservation
Resources student and staff members request to loan
 
A member logs online and requests to loan a resource from SEEC
Member
A student or staff member
Students/staff
Members reserve resources 
Student
Members that enrol into courses at the University
Members
Students make reservations to loan a resource
Staff
Members that teach courses at the University
Members
Staff make reservations to loan a resource
Acquisition
Requests made by members, so they have access to new/more resources
 
Members make acquisitions which is then accepted or denied by admin staff
Privilege
Freedom to loan a resource from the School of SEEC
 
Student members have privilege which allows them to loan items
Administrator
A member of staff who decides/organizes acquisition requests
 
Checks to see if new resources can be supplied to members
RELATIONSHIP TYPES:

Entity Name
Multiplicity
Relationship
Multiplicity
Entity Name
Category
1..1
ProvidesAccessTo
*..1
Location
Location
1..*
Holds
1..1
Movable Resource
Privilege
0..*
Has
1..1
Category
Resources
0..*
Requires
1..1
Location
Movable/Non-movable resource
 
{Mandatory, or}
 
Resources
Resources
*..1
BelongsTo
1..*
Loan
Members
1..*
Make
1..1
Reservations
Staff/Student
 
{Mandatory, or}
 
Members
Members
0..*
Requests
1..*
Acquisitions
Administrator
1..*
Accepts/Denies
1..*
Acquisitions
Student
1..1
Have
1..*
Privilege
Student
1..1
Is Given A
0..*
Course Offering
Privilege
0..*
Provides
0..*
Course Offering
Resources
1..1
Require
0..*
Reservations
ATTRIBUTE TYPES: 

Entity Name
Attributes
Description
Data Type & Length
Nulls
Multi-valued
Derived
Default
Resource
Resource ID
ID of the resource
Char(5)
No
No
No
 
Description
Describes the resource
Varchar(100)
No
No
No
 
Status
Status of a resource being loaned
Varchar(30)
No
No
No
 
Course Offering
Offering ID
ID of the offering loan
Char(6)
No
No
No
 
Name
Name of the loan
Varchar(50)
No
No
No
 
Semester Offered
Semester that the loan is offered
Int
No
No
No
 
Year Offered
Year that the loan is offered
Char(4)
No
No
No
 
Course Beginning Date
The date the course begins
Varchar(20)
No
No
No
 
Course End Date
The date the course ends
Varchar(20)
No
No
No
 
 

 

 

ATTRIBUTE TYPES (CONT.): 

Entity Name
Attributes
Description
Data Type & Length
Nulls
Multi-valued
Derived
Default
Member
Member ID
ID of a member
Char(5)
No
No
No
 
Name
Name of member
Varchar(30)
No
No
No
 
Email
Email of member
Varchar(30)
No
No
No
 
Address
Address of member
Varchar(50)
No
No
No
 
City
Status of member
Varchar(12)
No
No
No
 
State
State of Member
Varchar(30)
No
No
No
 
Postcode
Postcode of Member
Char(4)
No
No
No
 
Phone Number
Phone number(s) of member
Char(10)
No
No
No
 
Comments
Comments about member
Varchar(100)
No
No
No
 
Student Member
Member ID
ID of Member
Char(5)
No
No
No
 
Penalty Points
Reservation points a student has
Int
No
No
No
12
Staff Member
Member ID
ID of Member
Char(5)
No
No
No
 
Administrator
Admin ID
ID of Admin
Char(10)
No
No
No
 
Status
Status of the acquisition
Varchar(15)
No
No
No
 
Fund Code
Code of Fund
Char(5)
No
No
No
 
Vendor Code
Code of Vendor
Char(5)
No
No
No
 
Price
How much the resource costs
Varchar(7)
No
No
No
 
Notes
Extra notes about the new resource
Varchar(50)
No
No
No
 
ATTRIBUTE TYPES (CONT.):

Entity Name
Attributes
Description
Data Type & Length
Nulls
Multi-valued
Derived
Default
Category
Category Code
Code of the resource’s category
Char(5)
No
No
No
 
Name
Name of category
Varchar(30)
No
No
No
 
Description
Description of category
Varchar(100)
No
No
No
 
Time Allowed
Time a resource can be used
Varchar(10)
No
No
No
 
Location
Location ID
ID of location
Char(5)
No
No
No
 
Room
Room location
Char(3)
No
No
No
 
Building
Building location
Varchar(3)
No
No
No
 
Campus
Campus location
Varchar(20)
No
No
No
 
Movable Resource
Resource ID
ID of Resource
Char(5)
No
No
No
 
Immovable Resource
Resource ID
ID of Resource
Char(5)
No
No
No
 
Loan 
Resource ID
ID of offering
Char(10)
No
No
No
 
Member ID
ID of member
Char(10)
No
No
No
 
Date Loaned
Date resource is loaned
Varchar(30)
No
No
No
 
Date Due
Date resource is due
Varchar(30)
No
No
No
 
Time Loaned
Time resource is loaned
Varchar(5)
No
No
No
 
Time Due
Time resource is due
Varchar(5)
No
No
No
 
                                        

 

 

 

ATTRIBUTE TYPES (CONT.): 

Entity Name
Attributes
Description
Data Type & Length
Nulls
Multi-valued
Derived
Default
Enrolment
Offering ID
ID of Course Offering
Char(6)
No
No
No
 
Member ID
ID of Member enrolling
Char(5)
No
No
No
 
Privilege
Privilege ID
ID of Privilege a member has
Char(4)
No
No
No
 
Name
Name of privilege
Varchar(30)
No
No
No
 
Description
Description of privilege
Varchar(50)
No
No
No
 
 
What kind of privilege a student member has
Varchar(16)
No
No
No
 
Max Resources
Time allowed for a student to borrow/book a resource
Char(1)
No
No
No
 
Acquisition
Acquisition ID
ID of Acquisition
Char(5)
No
No
No
 
Acquisition Request
Requested Item
Varchar(30)
No
No
No
 
Item Name
Name of item
Varchar(30)
No
No
No
 
Make
Make of resource
Varchar(20)
No
No
No
 
Manufacturer
Manufacturer of resource
Varchar(20)
No
No
No
 
Model
Model of resource
Varchar(20)
No
No
No
 
Year
Age of resource
Char(4)
No
No
No
 
Item Description
Item description
Varchar(150)
No
No
No
 
Item Urgency
How much is the item needed
Varchar(12)
No
No
No
 
 

 

 

Relational Model Mapped with EER:

Location(LocationID, Room, Building, Campus)

Primary Key LocationID

Normalisation (BCNF):

Location(LocationID, Room, Building, Campus)
Category(CategoryCode, Name, Description, TimeAllowed) 
Primary Key CategoryCode

Normalisation (BCNF):

Category(CategoryCode, Name, Description, TimeAllowed)
Resource(ResourceID, CategoryCode, LocationID, Name, Description, Status) 
Primary Key ResourceID

Foreign Key CategoryCode references Category(CategoryCode)

ON UPDATE CASCADE, ON DELETE NO ACTION

Foreign Key LocationID references Location(LocationID)

ON UPDATE CASCADE, ON DELETE NO ACTION

Normalisation (BCNF):

Resource(ResourceID, CategoryCode, LocationID, Description, Status)
Acquisition(AcquisitionID, MemberID, AcquisitionRequest, ItemName, Make, Manufacturer, Model, Year, ItemDescription, ItemUrgency) 

Primary Key AcquisitionID

Foreign Key MemberID references Member(MemberID)

ON UPDATE CASCADE, ON DELETE NO ACTION

Normalisation (BCNF):

Acquisition(AcquisitionID, AcquisitionRequest, ItemName, ItemDescription, ItemUrgency) 

Item Name(ItemName, Make, Manufacturer, Model, Year)
Course Offering(OfferingID, Name, SemesterOffered, YearOffered, StartDate, EndDate) 
Primary Key Offering ID

Normalisation (BCNF):

Course Offering(OfferingID, CourseID, SemesterOffered, YearOffered, StartDate, End Date)

Course(CourseID, Name)
Privilege(PrivilegeID, Name, Description, CategoryCode, MaxResources)

Primary Key PrivilegeID

Foreign Key CategoryCode references Category(CategoryCode) 
ON UPDATE CASCADE, ON DELETE NO ACTION

Normalisation (BCNF):

Privilege(PrivilegeID, Name, Description, Privilege Category, MaxResources)
Movable Resource(ResourceID, Description, Status, Name, Make, Manufacturer, Model, Year, AssetValue)

Primary Key Resource ID

Normalisation (BCNF):

Movable Resource(ResourceID, Description, Status, Name, Model)

Item(Model, Make, Manufacturer, Year, AssetValue)
 

Relational Model Mapped with EER (CONT.):

Immovable Resource (ResourceID)

Primary Key ResourceID

Normalisation (BCNF):

Immovable Resource(ResourceID)
Loan(MemberID, ResourceID, DateLoaned, TimeLoaned, DateDue, TimeDue)
Primary Key MemberID

Foreign Key ResourceID references Resource(ResourceID)

ON UPDATE CASCADE, ON DELETE NO ACTION

Normalisation (BCNF):

Loan(MemberID, ResourceID, DateLoaned, TimeLoaned)

Member(MemberID)

Date(DateLoaned, DateDue)

Time(TimeLoaned, TimeDue)
Member(MemberID, Name, Email, Address, City, State, Postcode, Status, PhoneNumber, Comments) 
Primary Key MemberID

Normalisation (BCNF):

Member(MemberID, Name, Email, Address, City, State, Status, PhoneNumber, Comments)

Address(City, State, Postcode)
StudentMember(­MemberID)

Primary Key MemberID

Foreign Key (MemberID) references Member(MemberID) 

ON UPDATE CASCADE, ON DELETE NO ACTION

Normalisation (BCNF):               

StudentMember(­MemberID)
StaffMember(­MemberID)

Primary Key MemberID

Foreign Key (MemberID) references Member(MemberID) 

ON UPDATE CASCADE, ON DELETE NO ACTION

Normalisation (BCNF):

StaffMember(­MemberID)
Administrator(AdminID, Status, FundCode, VendorCode, Price, Notes)

Primary Key AdminID

Normalisation (BCNF):

Administrator(­­AdminID, Status, Notes, VendorCode)

Vendor(VendorCode, Price, FundCode)
 

 

 

 

The Normalisation Process/Method:

Administrator(Admin ID, Status, Fund Code, Vendor Code, Price, Notes)

Primary Key Admin ID

Normalisation:

R: Administrator(Admin ID, Status, Fund Code, Vendor Code, Price, Notes)

-        All the attributes inside of Administrator is known as R. 

FD1: Administrator - ­­Admin ID, Status, Notes, Vendor Code

-        This is the first functional dependency on R where Administrator is represented as X1 and everything on the right side of the arrow is represented as Y1. 

FD2: Vendor Code - Price, Fund Code 

-        Vendor Code is known as a functional dependency. Without the vendor code, price and fund code wouldn’t exist. Vendor Code is represented as X2 and Price and Fund Code are represented as Y2 in FD2. 

R-Y2: Admin ID, Status, Vendor Code, Notes

-        Y2 is removed from R to decompose the functional dependency. 

Vendor(Vendor Code, Price, Fund Code) - 3NF

-        Vendor Code and Admin ID are now candidate keys 

Administrator(­­AdminID, Status, Notes, VendorCode)

Vendor(VendorCode, Price, FundCode)

-        The functional dependency is now in BCNF. 

Member(Member ID, Name, Email, Address, City, Postcode, Status, Phone Number, Comments Field) 
Primary Key Student ID

Normalisation:

R= Member(Member ID, Name, Email, Address, City, Postcode, Status, Phone Number, Comments Field) 

-        All the attributes inside of Member is known as R. 

FD1: Member - Member ID, Name, Email, Address, City, Postcode, Status, Phone Number, Comments Field

-        This is the first functional dependency on R where Student is represented as X1 and everything on the right side of the arrow is represented as Y1. 

FD2: City, State - Postcode

-        City and State are known as a functional dependency. Without them postcode wouldn’t exist. City and State is represented as X2 whilst Postcode is represented as Y2 in FD2. 

R-Y2: Member ID, Name, Email, Address, City, State, Status, Phone Number, Comments Field

-        Y2 is removed from R to decompose the functional dependency. 

Postcode(City, State, Postcode) - BCNF

-        The functional dependency is now in BCNF.