1280160137160CIS017-1 Computer Systems Structure
-25404127500
1280160137160CIS017-1 Computer Systems Structure
CIS095-1 Databases and Computer Networking 2021-2022
Assignment 1 Design and Implement a Database
INDIVIDUAL REPORT
Student ID: XXXXX Full Name XXXXXX
00CIS017-1 Computer Systems Structure
CIS095-1 Databases and Computer Networking 2021-2022
Assignment 1 Design and Implement a Database
INDIVIDUAL REPORT
Student ID: XXXXX Full Name XXXXXX
Table of Contents
Introduction / Overview3
Task Description...3
Tasks:
Conceptual / Physical Database Design
Entity Relationship Model..
Normalisation
Physical Database Design (including Data Dictionary).
Implementation of the Database...
Query Design and implementation
Discussion / Critical Analysis / Reflection
Conclusion
References
Appendix
You will need to complete the Table of Contents and Page Nos manually
PLEASE NOTE THAT THIS IS A PROFESSIONAL REPORT
ALL SCREENSHOTS, DIAGRAMS, etc SHOULD BE GIVEN A FIGURE NUMBER AND A CAPTION AND AN EXPLANATION
CIS017-1 Computer Systems Structure 2020-2021CIS095-1 Databases and Computer Networking 2020-2021
Assignment 1 Design and Implement a Database
Introduction/Overview
Why are you doing this work? What were you asked to do? What did you do? Were you successful?
Task Description
This is basically a description of the task and case study as on the Assignment 1 brief. You can paraphrase what is written on the Assignment 1 brief.
Tasks:
Conceptual / Logical Database Design
Entity Relationship Model
An Entity Relationship Model consists of a Entity Relationship Diagram (ERD) and a list of Entities with their attributes, Primary Key and Foreign Keys.
Example:
Booking (BookingID, CustomerID*, RoomiD*, ManagerID*, DateBooked, ArrivalDate, DepartureDate, BookingStatus, Paid)
Primary and Foreign Keys and other attributes for each entity should be included in this list.
You are STRONGLY ADVISED to use VISUAL PARADIGM CE to create your Entity Relationship Diagram. Visual Paradigm Community Edition software is a free download. You should not use Visio or equivalent software.
Your ER Diagram should include all identified entities and the relationships between them. Relationship should include consideration of both cardinality and optionality, and a textual description of each relationship should be included.
Remember: All diagrams / figures / screenshots need a Fig No, caption and explanation.
Normalisation
Use a columnar format, for example:
Normalisation Hotel Booking System
UNF 1NF 2NF 3NF
CustomerIDCustomerIDCustomerIDCustomerIDTitle Title Title Title
FirstNameFirstNameFirstNameFirstNameLastNameLastNameLastNameLastNameEMailEMailEMailEMailMobileNoMobileNoMobileNoMobileNoPassword Password Password Password
Address1 Address1 Address1 Address1
Town Town Town Town
County County County County
PostCodePostCodePostCodePostCodePaymentMethodPaymentMethodPaymentMethodPaymentMethodRoomIDRoomTypeCustomerIDCustomerIDCustomerID*
FloorLevelRoomIDRoomIDRoomID*
DateBookedDateBookedDateBookedDateBookedArrivalDateRoomTypeArrivalDateArrivalDateDepartureDateFloorLevelDepartureDateDepartureDateBookingStatusArrivalDateBookingStatusBookingStatusPaid DepartureDatePaid Paid
ManagerIDBookingStatusManagerIDManagerID*
FirstNamePaid FirstNameLastNameManagerIDLastNameManagerIDEMailFirstNameEMailFirstNameMobileNoLastNameMobileNoLastNamePassword EMailPassword EMailMobileNoMobileNoPassword RoomIDPassword
RoomTypeFloorLevelRoomIDRoomTypeFloorLevelThis needs a Table No and caption and a detailed explanation of how you go from UNF to 1NF,1INF to 2NF and 2NF to 3NF.
Physical Database Design (including Data Dictionary)
At this point you will have decided what database platform (RDMS) you will use ie. Oracle, MySQL, SQLite.
This includes:
Skeleton Tables
List of tables with their attributes, Primary Key and Foreign Keys.
Example:
Booking (BookingID, CustomerID*, RoomiD*, ManagerID*, DateBooked, ArrivalDate, DepartureDate, BookingStatus, Paid)
Data Dictionary (for each table)
Example:
Customer
Description: Customer details
Field Name Datatype Length Index Null Default Validation rule Description
custid (Primary) int (11)
unsigned 11 PK No AutoincrementedUniquely identifies every customer
firstnamevarchar (30) 30 No First name of customer
lastnamevarchar (30) 30 No Last name of customer
email varchar (100) 100 No Must be email format containing an @ and a .
Regex expression used Email of customer
password varchar (30) 30 No Customer password
Indexes
KeynameType Unique Column Null
PRIMARY BTREE Yes custidNo
[Note: I cant get this positioned correctly! The original document is in the MISC folder on the left in BREO]
For each table, for each attribute show the datatype, length, and other properties. You may want to include any other information such as data entry constraints.
Take care with the datatypes as these vary from database system to database system.
Read the assignment brief for more details.
Implementation of the Database
For this practical task, you can use almost any RDMS MySQL, SQLite, Oracle, Oracle Application Express (APEX) but you should not use MS Access.
YOU NEED TO STATE WHAT RDMS YOU ARE USING.
Write and execute SQL statements to create your database table structures including Primary Key and Foreign Key constraints.
Write and execute SQL statements to insert at least two rows of data in each table. Further records can be added manually.
You should create screenshots of the implementation to show the successfully created tables structure and data, and the results of running your queries.
It may be possible (depending on platform and version) to also produce a screenshot of the set of tables created with the relationships between them.
Everything ie. SQL statements and output/result needs to be documented with screenshots or copy and pasted code.
Remember those Fig Nos, captions and explanations.
Query Design and Implementation
Use your SQL skills to design a set of relevant SQL queries and demonstrate your knowledge of SQL. Five (5) or six (6) queries will be sufficient but should include querying from multiple tables, using aliases, summary queries and grouping. The more complex and relevant the query is the more marks you are likely to achieve.
You show include the purpose of each query in terms of relevance to the business and in the context of the given scenario. You also need to include and exaplain the code, and show the results of running the code
Your SQL query designs should be included in your report, and you should include an explanation of the purpose of the query.
Execute SQL commands to create the queries you have designed. Run and test the queries.
Everything ie. SQL statements and output/result needs to be documented with screenshots or copy and pasted code.
Remember those Fig Nos, captions and explanations!
Discussion / Critical Analysis / Reflection
This part is important.
Basically, you are evaluating what you have done. Did you achieve all the required specification of the Assignment brief? If not, why not? Discuss the problems you encountered and how you overcame them.
Did you give yourselves enough time to accomplish this work? Was it harder than you expected? What have you learned from tackling this Assignment?
Did this work enhance your understanding of the database design and implementation programming language? Did it motivate you to learn more SQL and about NoSQL databases?
If you had to undertake this or something similar again would you tackle it differently?
What improvements or enhancements would you recommend for a future iteration of this project?
Conclusion
What were you asked to do? Did you achieve it?
References
Harvard Referencing System. Any reference must be cited in text
Appendix
If needed!
Anything else that you might want to include that does not fit well into the main report body.
CIS016-1 Principles of Programming 2019-2020CIS096-1 Principles of Programming and Data Structures 2019-2020
Assignment 2- Group / Individual Project Extra-Curricular Events Management System
DRAFT DESIGN
Functional Requirements
EEMS = Extra-Curricular Events Management System
Req. No Requirement Priority*
1 As student must be able to register on the EEMS MUST
2 A student must be able to log in to the EEMS MUST
3 A student must be able to log out of the EEMS COULD
4 An administrator must be able log in to the EEMS SHOULD
5 An administrator must be able to log out of the EEMS COULD
6 An administrator must be able to grant Organiser rights to a student SHOULD
7 An administrator must be able to revoke Organiser rights from a student SHOULD
8 A student must be able to search events SHOULD
9 A student must be able to view all events MUST
10 A student must be able to book an event MUST
11 A student must be able to view his/her bookings MUST
12 A student must be able to cancel a booking MUST
13 An organiser must be able to post an event MUST
14 An organiser must be able to view all his/her events SHOULD
15 An organiser must be able to edit an event SHOULD
16 An organiser must be able to cancel an event SHOULD
17 An administrator must be able to view all events SHOULD
18 An administrator must be able to search all events COULD
19 An administrator must be able to cancel an event SHOULD
20 An administrator must be able to view all bookings SHOULD
21 An administrator must be able to cancel a booking SHOULD
22 An administrator must be able to confirm a booking SHOULD
Non-functional Requirements
Req. No Requirement Priority*
1 The EEMS should process input and return results within 10 seconds 2 The EEMS should run on a range of devices from PCs to mobile phones 3 The EEMS design should be sufficiently scalable and flexible to allow for further future enhancements 4 The EEMS users should not experience critical system failures. 99.99% uptime should be achieved. Usability Requirements
Req. No Requirement Priority*
1 The EEMS should incorporate a user-centric design 2 The design should demonstrate evidence of a good understanding of interface design issues for example, a consistent design for each form, layout of content, use of colour schemes and images, navigational methods, usability when viewed at various screen resolutions and various monitor dimensions. 3 All data entry forms should be short and easy to complete and there should be entry validation. 4 The EEMS short have clear and intuitive navigation 5 The EEMS should comply with WW3 Web Accessibility Standards (WCAG)
Text easy to read and language and language style should be appropriate with absence of grammar / spelling errors There should be a clear layout which remains consistent throughout the application. Style, layout and content should be appropriate for the purpose of the application. *MOSCOW Notation:
M = MUST
S = SHOULD
C = COULD
W = WONT
Design
UML Diagrams
Use Case Diagram
-27305022352000
Activity Diagram
Class Diagram
Classes
HIDE THIS SECTION
Student (studentid, universityid, password, firstname, lastname, email, role)
Event (eventid, eventtitle, eventdescription, category, date, time, location, roomno, places, bookingrequired)
Booking (bookingid, datebooked, status)
Administrator (adminid, universityid, password, firstname, lastname, email)
Connection (connectionid, hostname, database, username, password)
Database Design
VERSION 1
Entity Relationship Model (ERM)
Entity Relationship Diagram (ERD)
6798945749300Admin
00Admin
497776513500101
001
355854013493748591551314450368159211830610.1.M
000.1.M
16775596756400.1.M
000.1.M
76003158112270046907737711941
001
95415710340561
001
953825700074makes
00makes
3545425644608is for
00is for
44924051105590has
00has
18447031073813is for
00is for
3561825104198035701368511761940118101815319560218750303578086102610599391210102025015616745876Event
00Event
2256155723320Booking
00Booking
-326058707501Student
00Student
46215301141730is for
00is for
186055997585posts
00posts
35433005245103558540514985196596049212519583404768851676400112395is organized by
00is organized by
6857365259080Connection
00Connection
3848100373380is organized by
00is organized by
15894056584950.1.M
000.1.M
37255456731000.1.M
000.1.M
2252980447040Event Organiser
00Event Organiser
-5829306858000
Entities
Student (studentid, universityid, password, firstname, lastname, email, role)
Event (eventid, eventtitle, eventdescription, category, date, time, location, roomno, places, bookingrequired)
Booking (bookingid, datebooked, status)
EventOrganiser (studentid, eventid)
Administrator (adminid, universityid, password, firstname, lastname, email)
Connection (connectionid, hostname, database, username, password)
Skeleton Tables
Student (studentid, universityid, password, firstname, lastname, email, role)
Event (eventid, eventtitle, eventdescription, category, date, time, location, roomno, places, bookingrequired)
Booking (bookingid, studentid*, eventid*, datebooked, status)
EventOrganiser (studentid*, eventid*)
Administrator (adminid, universityid, password, firstname, lastname, email)
Connection (connectionid, hostname, database, username, password)
Primary Key = Bold and Underlined
Foreign Key = *
VERSION 2 (SIMPLER)
Entity Relationship Model (ERM)
Entity Relationship Diagram (ERD)
5585460133858054254401355725558546013385804648201323341006798945749300Admin
00Admin
368159211830610.1.M
000.1.M
16775596756400.1.M
000.1.M
76003158112270046907737711941
001
95415710340561
001
953825700074makes
00makes
3545425644608is for
00is for
44924051105590has
00has
18447031073813is for
00is for
3561825104198035701368511761940118101815319560218750303578086102610599391210102025015616745876Event
00Event
2256155723320Booking
00Booking
-326058707501Student
00Student
11557010198101
001
572960510560050.1.M
000.1.M
44583351048385is organized by
00is organized by
5029201012825organises
00organises
457200131445006857365259080Connection
00Connection
-495300000
Entities
Student (studentid, universityid, password, firstname, lastname, email, role)
Event (eventid, eventtitle, eventdescription, category, date, time, location, roomno, places, bookingrequired)
Booking (bookingid, datebooked, status)
Administrator (adminid, universityid, password, firstname, lastname, email)
Connection (connectionid, hostname, database, username, password)
Skeleton Tables
Student (studentid, universityid, password, firstname, lastname, email, role)
Event (eventid, studentid*, eventtitle, eventdescription, category, date, time, location, roomno, places, bookingrequired)
Booking (bookingid, studentid*, eventid*, datebooked, status)
Administrator (adminid, universityid, password, firstname, lastname, email)
Connection (connectionid, hostname, database, username, password)
Primary Key = Bold and Underlined
Foreign Key = *
Physical Database Design
Data Dictionary
Student
Description: Student details
Field Name Datatype Length Index Null Default Validation rule Description
studenttid (Primary) int (11)
unsigned 11 PK No AutoincrementedUniquely identifies every student
universityidvarchar (10) 10 No University Id od student
password varchar (30) 30 No EEMS password
firstnamevarchar (30) 30 No First name of customer
lastnamevarchar (30) 30 No Last name of customer
email varchar (100) 100 No Must be email format containing an @ and a .
Regex expression used Email of student
role varchar (10) 10 No Student or Organiser
Indexes
KeynameType Unique Column Null
PRIMARY BTREE Yes custidNo
User Interface Design (UI)
.
ASSIGNMENT TOP SHEET
Faculty of Creative Arts, Technologies & Science
Department of Computer Science and Technology
Student Ref. No: Unit Code
CIS093-1 / CIS016-1
Unit Name
CIS096-1 - Principles of Programming and Data Structures
CIS016-1 - Principles of Programming Deadline for Submission(s)
10:00am Friday 27 May 2022
Student ID:
Student Name:
Unit Team
Susan Brandreth
Assignment Details:
Assignment 2 Group / Individual Assignment (Hotel Online Booking and Management System)
Instructions to Students:
Please note:
Work presented in an assessment must be your own. Plagiarism is where a student copies work from another source, published or unpublished (including the work of another student) and fails to acknowledge the influence of anothers work or to attribute quotes to the author. Plagiarism is an academic offence and the penalty can be serious. The Universitys policies relating to Plagiarism can be found in the regulations at http://www.luton.ac.uk/livingandstudying/qa/documents. To detect possible plagiarism we may submit your work to the national plagiarism detection facility. This searches the Internet and an extensive database of reference material including other students work. Once your work has been submitted to the detection service it will be stored electronically in a database and compared against work submitted from this and other universities. It will therefore be necessary to take electronic copies of your materials for transmission, storage and comparison purposes and for the operational back-up process. This material will be stored in this manner indefinitely.
I have read the above information and I confirm that this work is my own and that it may be processed and stored in the manner described.
Signature (Print Name): ...........................................................Date: .........................................
Extension deadline
Mitigation Team agrees that the assignment may be submitted ____ days after the deadline and should be marked without penalty.
Mitigation Team confirmation.................................................................................................................
All assignments must be electronically submitted using Turnitin (via BREO) by 10am on the due date. Please leave sufficient time to meet this deadline and do not leave the handing-in of assignments to the last minute. You need to allow time for any system problems or other issues.
CIS016-1 - Principles of Programming CIS096-1 Principles of Programming and Data Structures
Assignment No. 2 Group / Individual Assignment
The Learning Outcomes to be addressed in this assignment:
Carry out a software project collaboratively by implementing a program following the stages of analysis, design, implementation and testing using a modern high level programming language.
Please Note: Assignment 2 for these units is usually a Group Project and you would be expected to work in a group. However due to the constraints imposed by the COVID pandemic the Assignment 2 has been adapted to allow for a student to work as an Individual.
It is very important that, whether you are working in a group or as an individual, you read this assignment brief very carefully. You should also look at related documents and guidelines, and any guidance posted on BREO under Announcements.
It is YOUR responsibility to be aware of what you have to do and what you have to submit in order to successfully pass this Assessment.
Description
Work with colleagues in your assigned/formed group (or you can work as an individual) to develop and implement the case study which is described below.
Submit a word-processed Group or Individual Report detailing the work that you have done as part of the group (or as an individual). This Report must be professionally structured, and presented and have the required content. Please use the Report Template provided as guidance. If working in a group, all members of the group must be clearly identified (providing full name and student ID) on the title page and in the body of the report. If working as an individual, you need to clearly identify that the work is NOT group work but individual work.
IMPORTANT NOTE: If working as an individual you do not need to implement the entire scenario. For example, you might only implement the application with customer use cases. In addition, if working as an individual you can implement a text-based application rather than a GUI application.
You are strongly recommended to use the Report Template which will be provided.
Each group will be given a mark, but each group member will also be assigned an individual mark. Your individual mark will be influenced by your individual contribution to the development of your program as evidenced in your Group Report, and your contribution to the group presentation. See later. Due to the impact of COVID presentations on campus are unlikely to be possible. Therefore, every student will need to record and upload a video recording demonstrating the application and explaining at least some of the code.
If working as an Individual and we are back on Campus you would be expected to do an Individual presentation.
Groups
The size of the groups should not be larger than five (5) people. Assignments by a group of two (2) people are also acceptable in exceptional cases that need to be approved by the lecturer. However, you will not gain any advantage by doing so.
For those working in a Group:
You will not need to sign-up to a group on BREO (since there are two units involved). However, you will need to provide student ID numbers and names for all group members at the front of the submitted Group Report. You can also give your group an appropriate name. Each member of a Group needs to submit the Group Report. If you do not submit a copy of your Group Report you WILL NOT pass. If your name does not show on the report, this means you have not attempted this assignment.
If there is a dispute within the group, you should inform the lecturer / unit coordinator, so that a meeting with the group can be held where the matter can be resolved.
Tasks
Consider the task description as a group (or individual). In your group, agree on the features to be part of your solution to the problem described, and on how you might be able to deliver these features. (This includes determining what data may be required to support the functionality in each case). List and describe any assumptions you make.
Develop an appropriate database to store all the required Hotel Online Booking and Management System information. Develop the required client / database or client/server system with a GUI-driven client and a server that handles appropriate requests (see Task Description).
Code and test the critical features of the Hotel Online Booking and Management System you have identified by analysis using the range of programming features of the Java language such as I/O file operations, JDBC, Networking classes etc.
Hints:
Develop GUIs for each of the forms you need to use within the system as user interface. Note that you have 4 different users: Customer, Corporate Client, Receptionist, and Bar/Restaurant Staff.
Make sure the database is deployed on the server side, and the users access this via a GUI-based client program.
Please carefully design your database using your database design and SQL skills. (Please refer to the Report Template provided which has guidance on what you should include).
Deliverables
Group Project Report (10-20 pages with Code Appendices)
Illustrate and justify your choices in analysis and implementation. Provide evidence that you actually tested your code. Include and explain your class diagram. Any code may go into the appendix, except core classes that you may explain in the main text. Please do not exceed the page limit as you may be penalised. (The pages in the Appendix do not count in the total page count.)
This is a formal report and attention should be given to the format of this work. You should include a title page, table of contents, heading and sub-headings (introduction, main part, summary, appendices), and Fig. Nos. and captions for diagrams, screenshots, and graphics. You are strongly advised to use the report template provided.
A Group / Individual Report template is provided on BREO.
Video / Presentation / Demonstration (5 slides max with demonstration of your program - approx. 5 mins total)
Provide an overview of your work as a group and demonstrate your working system.
OR
Due to the constraints imposed by COVID-19 it may well not be possible for you present your Project work on campus.
In this case, every group member (and individual if working on an Individual Project) will be required to record and upload an individual video demonstrating their working application and explaining parts of the code. [Max. 5 minutes duration.]
Complete program code submitted as a zipped file.
Note: The lecturers / demonstrators will be happy to advise you at any stage during the development of this program.
Grading:
Marking Grid
G F E D C B A
0 1 - 34 35 - 39 40 - 49 50 - 59 60 - 69 70 - 100
JDBC (30%) No submission. Poor. Little discernible merit. Database created, but design not correct. Database created, but connectivity is poor. Good database design. Connectivity is effective. Very good database design. JDBC connectivity working effectively. Excellent database design and connectivity. JDBC works well.
GUIs and Forms (30%) No submission. Poor. Little discernible merit. Forms are created but not working. Forms are created and working. Forms are complete with an acceptable design. Forms are working well and complete with a good design. Forms are working and integrated with excellent design.
Report (30%) No submission Poor. Little discernible merit. Not structured and incomplete. Basic structure and basic content. Good structure, and acceptable content. Very good structure and content. Excellent structure and content.
Presentation (10%) No submission Poor. Little discernible merit. Demonstrates a poor level of subject knowledge. Weak. Demonstrates a weak level of subject knowledge. Acceptable. Demonstrates an acceptable level of subject knowledge. Good presentation. Demonstrates a good level of subject knowledge. Very good presentation. Demonstrates a very good level of subject knowledge. Professional presentation. Demonstrates an excellent level of subject knowledge.
Submission details:
The Group / Individual Report MUST be named with the Group Name or Student ID.
Upload the .doc, .docx or .pdf file via the Assignment 2 Group / Individual Report submission link provided on BREO.
The zipped program code MUST be named with your Group Name and Student ID (or just Student ID if submitting individual work)
Video Recording Demonstrating and explaining your program. 5 minutes maximum. This needs to be uploaded as an individual recording by every student
Submission of both reports and your zipped program code must take place before 10:00am on Friday 27 May 2022 using the appropriate links provided on BREO.
Capture and save evidence of submissions in a word document.
You will be asked to demonstrate that the work submitted is all your own work. Failure to convince us that the work is your own during the presentation may result in failure of the whole group or individual group members!
Please note that all submissions with will checked for similarity to other sources, and evidence of plagiarism may be severely penalised.
Task Description
Hotel Online Booking and Management System
You need to create a GUI desktop application to meet the requirements of this scenario. A database needs to be created to store, at least, customer information, booking information, as well as room information.
The task of your group (or as an individual) is to implement the Hotel Online Booking and Management System containing the functionality shown in the Use Case Diagram in REF _Ref224122193 h * MERGEFORMAT Error! Reference source not found., taking the different user roles (customer, corporate organization, receptionist, and bar/restaurant staff) into account.
Consider the following scenario:
Luton Hotel is a large independently run hotel (ie. it is not part of a chain) located in the town of Luton. It offers a variety of accommodation (single, twin and double rooms) all of which offer en suite facilities, a telephone with an outside line and a mini-bar. The hotel has a bar and a restaurant, where guests may take refreshment and charge this to their room bill. Room service is also available. Services not offered by the hotel itself (for example, dry cleaning) can be ordered by guests through the hotel receptionist and charged to their room.
The hotel would like to provide an online service for customers (individual or corporate) to book rooms and to check their room bookings.
Non-corporate customers must register online before making a booking. Companies need to set up an account with the hotel before making a corporate booking and they are billed at the end of each month for bookings that have been completed since the last invoice was sent. A typical corporate client is Luton University, and during months such as February and June when examination boards are taking place as many as 30 or 40 guests may be staying at the hotel on the University account. At other times there may be only a few. A discount is negotiated for each corporate client when their account is set up and is reviewed annually.
Non-corporate customers who are not registered yet need to register (providing their address, phone number, email address and credit card information). A registered customer should be able to book a room, ie. provide the check-in and check-out date and if a double or single room is required. Customers should also be able to view their bookings and change or cancel them if needed. All these operations are only possible after a customer signs in (which means they must be registered). Your system should also check if the requested room (single, twin or double room) is available for the time of stay.
After a booking is made, receptionists must assign an appropriate room to each booking, matching the customers demand regarding single twin or double room. To determine whether a room is available during the requested time period, the receptionist should be able to see existing bookings per room.
A database needs to be created to store customer/corporate client data (name, address, email, telephone number etc) and booking information (start and end date, single, twin or double room) as well as room information, indicating whether a room is a single, twin or double room. Each booking must be assigned to exactly one single, twin or double room as indicated in the booking.
When a booking request is made the receptionist first checks whether there is a suitable room or rooms available on the required dates. For corporate clients, a guaranteed booking is then made. For individual clients, the Hotel requests credit details before the booking can be guaranteed. If these are not provided the booking is considered unguaranteed. In either case, the booking is now considered to be active. At any point up to when the guests check in, the booking can become guaranteed by the customer providing credit card details. A booking can be cancelled at any point up to when the guests check in.
When the guest checks in, the booking becomes active. At the end of their stay the guest checks out and their booking is completed. If they are an individual guest they must at this point pay their bill in full, and it becomes paid. Corporate guests must check out and their booking becomes completed, but is only paid when the monthly invoice is sent to the corporate client. Booking records are kept on the system for six months before those that are cancelled or paid are archived.
Figure 1: Hotel Online Booking and Management System Use Case Diagram
As a guide, the functionality of the Hotel Online Booking and Management System is shown in the Use Case Diagram in Figure 1, taking the different user roles (customer, corporate client, receptionist, and bar/restaurant staff) into account.
Use Case Specifications/Description
Allocate Accommodation
A specific room is allocated to each guest.
Change Booking
An unguaranteed booking may become guaranteed on provision of credit card details. Other changes (for example to the composition of the party or to the dates booked) may be made on the customers request.
Calculate Bill Total
The total bill for a room comprises the cost of the room plus any items charged to it (for example, from the minibar, hotel bar or restaurant and any additional services provided) plus the appropriate rate of VAT.
Cancel Booking
A customer may cancel a booking any time before checking in. The booked accommodation is de-allocated.
Charge Item to Room
A guest may use items from the minibar in the room, make external telephone calls, make purchases from the hotel bar or restaurant or order additional services such as laundry. These items are charged to the guests room. An external service ordered by the guest may also be charged to their room (although some services, such as taxis, will be paid for directly by the guest).
Check In Guest
When a party (one or more guests) arrives at the hotel each guest is checked in and the booking becomes registered. Details of all the guests are confirmed, or if they have not been given at the time of booking they are recorded. The method of payment (by which guest(s) or whether to be charged to a corporate account) is confirmed. Accommodation is allocated if not already done at the time of booking and special requests are recorded.
Check Out Guest
Before the guest leaves the hotel they need to check out and the booking becomes completed. The receptionist provides them with the total room bill, which an individual customer must pay. If the bill is to be charged to a corporate customer the guest is asked to check and sign the bill, it is then added to the appropriate corporate account.
Make a Booking
When a customer contacts the receptionist with a booking request the receptionist checks the room occupancy for the required period and if the customer's needs can be met, makes a booking.
A guaranteed booking is only made for corporate customers or for individual customers who supply credit card details.
Accommodation may be allocated at this stage; alternatively where it is a group booking (for example, for 6 twin-bedded rooms) the allocation of each guest in the party to a specific room may be done on checking in. Either way, occupancy records are updated to avoid double booking.
Order External Service
Services not offered by the hotel itself can be ordered, once their availability has been checked. The guest name, room number and details of the service required are taken when the service is ordered.
Provide Guest with Current Bill
On request a guest may be provided with the current total of their bill.
Please note that client-server architecture (network/sockets programming) as outlined below is not essential in order to achieve very high marks for this assignment.
Ideally, the whole system should be based on a client-server architecture, where the database is deployed on the server, and the users are given access to the database via the client graphical user interface (GUI). The server should speak to the database and the client GUI should speak to the server only (not to the database). REF _Ref276655084 h * MERGEFORMAT Figure 1 depicts this situation.
9144001237614002514600323215Server
00Server
4457700437515Database
00Database
0551815ClientGUI
00ClientGUI
124460099695Request
00Request
3695700116840SQL
Statement
00SQL
Statement
-1651008699400
2692400115570Result
00Result
177800115570Response
00Response
2692400126900
269240016573400
791210122555Figure SEQ Figure * ARABIC 1: System Architecture
00Figure SEQ Figure * ARABIC 1: System Architecture
Clients should send simple requests with parameters (for instance book_room, customer135, 08/03/2021, c244, 9 to send a new booking to the database or view_bookings customer123 to view the bookings for a specific customer) to the server. The server should process these requests and create suitable SQL statements. Results from the database should be set up accordingly and sent to the client as a response. For instance, if the request was view_bookings customer135 the server should return the bookings assigned to customer135 fetched from the database. One task of the server will be to translate the requests to SQL statements and turn the results from the database into a proper response to the client.
To get the maximum full marks (100/100), you do not need to implement a client/server solution as indicated above. Groups who do not follow this architecture (where for instance their GUI directly accesses the database) will still be able to pass with the maximum achievable marks. Groups who implement the required functionality but without a database/JDBC and a server will attract 60/100 marks maximum. You need 40 marks to pass. This is a group assignment but an individual assessment, but single group members may attract different marks based on their contribution to the group (determined for instance by peer assessment and the presentation).
Background: Why a client/server application?
You may ask yourself, Why do we have to develop a server? Isnt it sufficient when the client GUI speaks to the database directly?. The reason is that many of todays applications are like this. (If you create a Facebook client, do you want it to talk to the Facebook database directly or wouldnt it better to make use of Facebooks API, which would be more convenient?).
One of the advantages of the client/server architecture is that it provides a simple API for clients so they dont have to bother with storage and database issues.
Consider the case where you no longer want to store the data in a database, but in some other data structure - for instance files, a Cloud storage or a NoSQL database. (Services like Google and Facebook have been changing their backend storage several times in their history). If each client speaks to the database directly (using embedded SQL commands), you would have to reimplement each single client (and there can be several client implementations around for a server). In a client/server scenario, you would only need to update the server implementation to cope with your new storage. No client needs to be touched. Therefore, there are good reasons to provide an API and a further abstraction layer such as we propose in this assignment.
ASSIGNMENT TOP SHEET
Faculty of Creative Arts, Technologies & Science
Department of Computer Science and Technology
Student Ref. No: Unit Code
CIS017-1 / CIS095-1
Unit Name
Computer Systems Structure
Database and Computer Networks Deadline for Submission(s)
Friday, 29 June 2022 10:00am
Student Name:
Unit Team
Sue Brandreth
Assignment Details:
Referral Assignment 1 Design and Implement a Database
Instructions to Students:
Please note:
Work presented in an assessment must be your own. Plagiarism is where a student copies work from another source, published or unpublished (including the work of another student) and fails to acknowledge the influence of anothers work or to attribute quotes to the author. Plagiarism is an academic offence and the penalty can be serious. The Universitys policies relating to Plagiarism can be found in the regulations at http://www.luton.ac.uk/livingandstudying/qa/documents. To detect possible plagiarism we may submit your work to the national plagiarism detection facility. This searches the Internet and an extensive database of reference material including other students work. Once your work has been submitted to the detection service it will be stored electronically in a database and compared against work submitted from this and other universities. It will therefore be necessary to take electronic copies of your materials for transmission, storage and comparison purposes and for the operational back-up process. This material will be stored in this manner indefinitely.
I have read the above information and I confirm that this work is my own and that it may be processed and stored in the manner described.
Signature (Print Name): ...........................................................Date: .........................................
Extension deadline
Mitigation Team agrees that the assignment may be submitted ____ days after the deadline and should be marked without penalty.
Mitigation Team confirmation.................................................................................................................
All assignments must be electronically submitted using Turnitin (via BREO) by 10am on the due date. Please leave sufficient time to meet this deadline and do not leave the handing-in of assignments to the last minute. You need to allow time for any system problems or other issues.
CIS017-1 -Computer Systems StructureCIS095-1 Databases and Computer Networks
Assignment 1 Design and Implement a Database
Assignment 1 requires you to design and implement a relational database for the given scenario.
This is an individual assignment and each student should work independently and submit their assignment as an individual piece of work.
You should read this assignment brief very carefully, and, if it is not clear what you are asked to do, you should seek clarification.
Assignment 1 is worth 30% of the total unit mark.
The following Learning Outcomes will be addressed in this assignment:
Demonstrate the following knowledge and understanding:
Comprehend the underpinning technologies of data communications and computer networking while appraising the basic system structures and the principles around data modification.
Demonstrate the following skills and abilities:
Utilise problem-solving skills to put together information from different sources in order to design, develop and implement a databases or computer network.
In order to pass Assessment 1 you will need to:
Investigate a given busines-related case study / scenario
Determine the database requirements for the scenario
Apply conceptual / logical modelling techniques to create an Entity Relationship Model and a fully normalised set of tables
Implement and modify a database using a set of SQL commands.
Edit and manipulate database records based on requirements given to you
Successfully detect, describe and analyse the main cause of faults within data structures.
Case Study
Consider the following scenario:
A hotel would like to provide an online service for customers to book rooms and to check their upcoming trips. A database needs to be created to store customer data (name, address, email, telephone number) and booking information (start and end date, double or single room) as well as room information, indicating whether a room is a single or double room. Each booking must be assigned to exactly one single or double room as indicated in the booking.
Customers who are not registered yet need to register (providing their address, phone number, email address and credit card information). A registered customer should be able to book a room, ie. provide the check-in and check-out date and if a double or single room is required. Customers should also be able to view their bookings and change or cancel them if needed. All these operations are only possible after a customer signs in (which means they must be registered). Your system should also check if the requested room (single or double room) is available for the time of stay. After a booking is made, hotel managers must assign an appropriate room to each booking, matching the customers demand regarding single or double room. To determine whether a room is available during the requested time period, the hotel manager should be able to see existing bookings per room. Hotel managers must also sign in before any operation can be performed.
-1460541275000
Figure 1: Hotel Booking System Use Case Diagram
As a guide, the functionality of the Hotel Booking System is shown in the Use Case Diagram in Figure 1, taking the different user roles (customer and hotel manager) into account.
Tasks
Entity Relationship Model (ERM)
Create an Entity Relationship Model (ERM) for the Hotel Booking System.
You are allowed to use any ERM modelling notation but it is suggested that you use a version of the Oracle ERM notation as described during the Units lectures.
It also is suggested that you create your ER Diagram using Visual Paradigm Community Edition software which is a free download. You should not use Visio or equivalent software.
Your ER Diagram should include all identified entities and the relationships between them. Relationship should include consideration of both cardinality and optionality, and a textual description of each relationship should be included.
You should also create a list of your Entities.
Primary and Foreign keys and other attributes for each entity should be included in this list.
Your Entity Relationship Model should be included in your report.
Normalisation
Create a normalized set of tables for the scenario for the given data.
The steps in the normalization from UNF to 3NF should be shown.
Your set of normalized tables should be included in your report.
Physical Table Design (including Data Dictionary)
You should now be able to create a list of the tables to be implemented in your selected RDMS.
Create a set of Skeleton tables which should show the table name, primary key field, foreign key field(s), and all other attributes. (This will be RDMS system independent).
Create a Data Dictionary for all tables and attributes.
For each attribute show the datatype, length, and other properties. You may want to include any other information such as data entry constraints.
The Data Dictionary should be part of your physical design ie. dependent on the selected RDMS.
Your skeleton tables and data dictionary should be included in your report.
Implementation of the Database
For this last practical task, you can use almost any RDMS MySQL, SQLite, Oracle, Oracle Application Express (APEX) but you should not use MS Access.
Write SQL statements to create your database and table structure including Primary Key and Foreign Key constraints.
Write SQL statements to insert at least two rows of data in each table. Further records can be added manually.
You should create screenshots of the implementation to show the successfully created tables structure and data, and the results of running your queries.
It may be possible (depending on platform and version) to also produce a screenshot of the set of tables created with the relationships between them.
Query Design and Implementation
Use your SQL skills to design a set of relevant SQL queries and demonstrate your knowledge of SQL. Five (5) or six (6) queries will be sufficient but should include querying from multiple tables, using aliases, summary queries and grouping. The more complex and relevant the query is the more marks you are likely to achieve.
Write SQL statements to create the queries you have designed. Run and test the queries.
Your SQL queries should be included in your report, and you should include an explanation of the purpose of the query.
Report
You will need to submit a word-processed report with evidence, explanation and discussion of the above tasks.
This must be a professionally presented report with appropriate headers and footers (including filename, page numbers) and heading and subheadings. Any diagrams included must have a figure number and caption. A table of contents is optional.
Suggested sections are:
Introduction
Scenario
Tasks:
Entity Relationship Model
Normalization
Physical Table Design (including Data Dictionary)
Implementation of the Database
Query Design and Implementation
Discussion / Critical Analysis / Reflection
Your final report should be uploaded to the appropriate link on BREO before the deadline of Friday 29 July, 2022 10:00am
Deliverables
Assignment Report (maximum 10 pages with Appendices if required)
In-class demonstration of your database implementation
Please note that due to the difficult circumstances resulting from COVID-19 this may not be possible, and alternative method to demonstrate your application may be required.
Grading:
Marking Grid
G F E D C B A
0 1 - 34 35 - 39 40 - 49 50 - 59 60 - 69 70 - 100
Entity Relationship Model (ERM) (20%) No submission. Poor. Little discernible merit. Entity Relationship model created but not correct. Entity Relationship Model created but some errors. Good attempt at creating an accurate Entity Relationship Model. Very good attempt at creating an accurate Entity Relationship Model. Excellent and accurate Entity Relationship model which fully meets the system requirements.
Normalisation (20%) No submission. Poor. Little discernible merit An attempt at creating a set of 3NF normalised tables but not correct. An attempt at creating a set of 3NF normalised tables but some errors. Good attempt at creating an accurate set of 3NF normalised tables. Very good attempt at creating an accurate set of 3NF normalised tables. Excellent and accurate set of 3NF normalised tables which fully meets the system requirements.
Table Design (10%) No submission. Poor. Little discernible merit An attempt at creating a set of skeleton tables and data dictionary but not correct. An attempt at creating a set of skeleton tables and data dictionary but some errors. Good attempt at producing an accurate set of skeleton tables and data dictionary. Very good attempt at producing an accurate set of skeleton tables and data dictionary. Excellent and accurate set of skeleton tables and data dictionary which fully meets the system requirements.
Query Design (20%) No submission. Poor. Little discernible merit. An attempt at producing a set of basic SQL query designs but not correct.
. An attempt at producing a set of basic SQL query designs but some errors.
Good attempt at producing an accurate but basic set of SQL query designs.
Very good attempt at producing an accurate set of SQL query designs.
Excellent and accurate set of SQL query designs which are relevant to the system requirements.
Query designs demonstrate a range of complexity as specified.
Database Implementation (20%) No submission. Poor. Little discernible merit. An attempt at implementation of the database design and SQL queries but not correct. An attempt at implementation of the database and query designs but some errors. Good attempt at implementation of the database and query designs. Very good attempt at implementation of the database and query designs. Excellent and accurate implementation of the database and query designs which fully meets the system requirements
Report (10%) No submission Poor. Little discernible merit. Not structured and incomplete. Basic structure and basic content showing some evidence and explanation of assignment tasks. Good structure, and acceptable content showing evidence and explanation of assignment tasks. Very good structure and content showing evidence, explanation and discussion of assignment tasks. Excellent structure and content showing evidence, explanation and discussion of assignment tasks.
Submission details:
The report should be uploaded as a .doc, .docx or .pdf file via the Assignment 1 Report submission link provided on BREO. It MUST be named with your Student ID ie. Ass1_12345.
Submission of your report and your zipped program code must take place before Friday 1 April, 2022 10:00am using the appropriate link provided on BREO.
Capture and save evidence of submissions in a word document.
You may be asked to demonstrate that the work submitted is all your own work.
Please note that all submissions will checked for similarity to other sources, and evidence of plagiarism may be severely penalised.
