[Solved] COMP1140 Assignment2

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 enroll in courses offered by SEEC. Course information about course offerings and student enrollments 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
• other notes pertaining to the request

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 an 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 privileges members have
• Provide information about the maximum possible loans a student member can take out

Business Rules:

1. Expiration of Student Member Access
   • A students 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 members 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 locationsVirtually 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 enroll 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
Catalogue		{Mandatory}		Resources
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
Catalogue	Physical Resources	Items that can be loaned physically	Varchar(50)	No	No	No
	Online Resources	Items that can be loaned virtually	Varchar(50)	No	No	No
Resources	Resource ID	ID of the resource	Char(10)	No	No	No	10
	Description	Describes the resource	Varchar(50)	No	No	No
	Status	Status of a resource being loaned	Varchar(30)	No	No	Yes
Course Offering	Offering ID	ID of the offering loan	Char(10)	No	No	No	10
	Course ID	ID of the course	Char(10)	No	No	No	10
	Name	Name of the loan	Varchar(30)	No	No	No
	Semester Offered	Semester that the loan is offered	Char(1)	No	No	No
	Year Offered	Year that the loan is offered	Char(4)	No	No	No
	Course Beginning/End Date	Date of the courses beginning/ending	Varchar(20)	No	Yes	No

ATTRIBUTE TYPES (CONT.):

Entity Name	Attributes	Description	Data Type & Length	Nulls	Multi-valued	Derived	Default
Members	Member ID	ID of a member	Char(10)	No	No	No	10
	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
	Status	Status of member	Varchar(12)	No	No	No
	Phone Number	Phone number(s) of member	Char(10)	No	No	No	10
	Comments Field	Comments about member	Varchar(100)	No	No	No
Administrator	Admin ID	ID of Admin	Char(10)	No	No	No	10
	Status	Status of the acquisition	Varchar(15)	No	No	No
	Fund Code	Code of Fund	Char(5)	No	No	No	5
	Vendor Code	Code of Vendor	Char(5)	No	No	No	5
	Price	How much the resource costs	Decimal(5)	No	No	No	5
	Other Notes Pertaining to the Request	Extra notes about the new resource	Varchar(50)	No	No	No
Reservations	Pickup Date and Time	Date and time resources are to be given	Varchar(20)	No	No	No
	Return Date and Time	Date and time resources are due	Varchar(20)	No	No	No

ATTRIBUTE TYPES (CONT.):

Entity Name	Attributes	Description	Data Type & Length	Nulls	Multi-valued	Derived	Default
Student and Staff	IDs	ID of student/staff	Char(10)	No	No	No	10
	Name	Name of student/staff	Varchar(30)	No	No	No
	Email	Email of student/staff	Varchar(30)	No	No	No
Category	Category Code	Code of the resources category	Char(5)	No	No	No	5
	Name	Name of category	Varchar(30)	No	No	No
	Description	Description of category	Varchar(50)	No	No	No
	Max Time Allowed to Borrow/Book	Time a resource can be used	Varchar(10)	No	No	No
Location	Location ID	ID of location	Char(8)	No	No	No	8
	Room	Room location	Char(3)	No	No	No	3
	Building	Building location	Varchar(3)	No	No	No
	Campus	Campus location	Varchar(15)	No	No	No
Movable Resources	Name	Name of resource	Varchar(20)	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	Char(12)	No	No	No	12
	Year	Age of resource	Char(4)	No	No	No	4
	Asset Value	Value of resource	Decimal(5)	No	No	No	5
Loan	Resource ID	ID of offering	Char(10)	No	No	No	10
	Member ID	ID of member	Char(10)	No	No	No	10
	Date/Time Loaned	Date/time resource is loaned	Varchar(30)	No	No	No
	Date/Time Due	Date/time resource is due	Varchar(30)	No	No	No

ATTRIBUTE TYPES (CONT.):

Entity Name	Attributes	Description	Data Type & Length	Nulls	Multi-valued	Derived	Default
Non-movable resources	Maximum Room Capacity	How many people can fit in a room	Varchar(30)	No	No	No
	Room Name	Name of Room	Char(3)	No	No	No	3
	Building	Building location	Varchar(3)	No	No	No
	Campus	Campus location	Varchar(15)	No	No	No
Privilege	Name	Name of privilege	Varchar(30)	No	No	No
	Description	Description of privilege	Varchar(50)	No	No	No
	Privilege Category	What kind of privilege a student member has	Varchar(16)	No	No	No
	Maximum Number of Resources allowed to be Booked/Borrowed	Time allowed for a student to borrow/book a resource	Varchar(10)	No	No	No
Acquisitions	Person Requesting Acquisition	Name of person	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	Char(12)	No	No	No	12
	Year	Age of resource	Char(4)	No	No	No	4
	Description of Required Item	Item description	Varchar(50)	No	No	No
	Item Urgency	How much is the item needed	Varchar(10)	No	No	No

Relational Model Mapped with EER:

Location(Location ID, Room, Building, Campus)Primary Key Location IDNormalisation (BCNF):Location(Location ID, Room, Building, Campus)

Category(Category Code, Name, Description, Max Time Allowed to Borrow/Book) Primary Key Category CodeNormalisation (BCNF):Category(Category Code, Name, Description, Max Time Allowed to Borrow/Book)

Resource(Resource ID, Physical Resources, Online Resources, Description, Status) Primary Key Resource IDNormalisation (BCNF):Resource(Resource ID, Physical Resources, Online Resources, Description, Status)

Acquisition(Acquisition ID, Person Requesting Acquisition, Item Name, Make, Manufacturer, Model, Year, Description for Required Item, Item Urgency) Primary Key Acquisition IDNormalisation (BCNF):Acquisition(Acquisition ID, Person Requesting Acquisition, Item Name, Description for Required Item, Item Urgency)Item Name(Item Name, Make, Manufacturer, Model, Year)

Course Offering(Offering ID, Course ID, Name, Semester Offered, Year Offered, Course Beginning/End Date) Primary Key Offering ID, Course IDNormalisation (BCNF):Course Offering(Offering ID, Course ID, Semester Offered, Year Offered, Course Beginning/End Date)Course(Course ID, Name)

Privilege(Privilege ID, Name, Description, Privilege Category, Maximum Number of Resources Borrowed/Booked at any given time)Primary Key Privilege IDNormalisation (BCNF):Privilege(Privilege ID, Name, Description, Privilege Category, Maximum Number of Resources Borrowed/Booked at any given time)

Movable Resource(Movable Resource ID, Resource ID, Description, Status, Name, Make, Manufacturer, Model, Year, Asset Value)Primary Key Movable Resource IDForeign Key Resource ID references Resource(Resource ID)ON UPDATE CASCADE, ON DELETE NO ACTIONNormalisation (BCNF):Movable Resource(Movable Resource ID, Resource ID, Description, Status, Name, Model)Items(Model, Make, Manufacturer, Year, Asset Value)

Relational Model Mapped with EER (CONT.):

Non-Movable Resource (Non-Movable Resource ID, Resource ID, Description, Status, Maximum Room Capacity, Room Name, Building, Campus)Primary Key Non-Movable Resource IDForeign Key Resource ID references Resource(Resource ID)ON UPDATE CASCADE, ON DELETE NO ACTIONNormalisation (BCNF):Non-Movable Resource(Non-Movable Resource ID, Resource ID, Description, Status, Name, Model)Items(Model, Make, Manufacturer, Year, Asset Value)

Loan(Resource ID, Member ID, Student ID, Staff ID, Date Loaned, Time Loaned, Date Due, Time Due)Primary Key Resource IDForeign Key Member ID references Member(Member ID)ON UPDATE CASCADE, ON DELETE NO ACTIONForeign Key Student ID references Student(Student ID)ON UPDATE CASCADE, ON DELETE NO ACTIONForeign Key Staff ID references Staff(Staff ID)ON UPDATE CASCADE, ON DELETE NO ACTIONNormalisation (BCNF):Loan(Resource ID, Member ID, Date Loaned, Time Loaned)Member(Member ID, Student ID, Staff ID)Date(Date Loaned, Date Due)Time(Time Loaned, Time Due)

Staff(Staff ID, Name, Email, Address, City, State, Postcode, Status, Phone Number, Comments Field) Primary Key Staff IDNormalisation (BCNF):Staff(Staff ID, Name, Email, Address, City, State, Status, Phone Number, Comments Field)Address(City, State, Postcode)

Student(Student ID, Name, Email, Address, City, State, Postcode, Status, Phone Number, Comments Field) Primary Key Student IDNormalisation (BCNF):Student(Student ID, Name, Email, Address, City, State, Status, Phone Number, Comments Field)Address(City, State, Postcode)

Administrator(Admin ID, Status, Fund Code, Vendor Code, Price, Other Notes Pertaining Request)Primary Key Admin IDNormalisation (BCNF):Administrator(Admin ID, Status, Other Notes Pertaining Request, Vendor Code)Vendor(Vendor Code, Price, Fund Code)

The Normalisation Process/Method:

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

Primary Key Admin ID

Normalisation:

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

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

FD1: Administrator -> Admin ID, Status, Other Notes Pertaining Request, 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 wouldnt 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, Other Notes Pertaining Request

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

Vendor(Vendor Code, Price, Fund Code) -> BCNF

• The functional dependency is now in BCNF.

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

Normalisation:

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

• All the attributes inside of Student is known as R.

FD1: Student -> Student 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 wouldnt exist. City and State is represented as X2 whilst Postcode is represented as Y2 in FD2.

R-Y2: Student ID, Name, Email, Address, City, 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.