Share on Google+Share on Google+

0 Answer(s)      7 years ago
Posted in : SQL
The University Accommodation Office Data requirements specification
Data requirements

The data stored on each full-time student includes: the matriculation number, name (first and last name), home address (street, city, postcode), date of birth, sex, nationality, smoker (yes / no), special needs, any additional comments, current status (placed / waiting), and what course the student is studying on.
The student information stored relates to those currently renting a room and those on the waiting list. Students may rent a room in a hall of residence or student flat.
When a student joins the University, he or she is assigned to a member of staff who acts as his or her Advisor of Studies. The Advisor of Studies is responsible for monitoring the student's welfare and academic progression throughout his or her time at University. The data held on a student's Advisor includes full name, position, name of department, internal telephone number, and room number.

------------------------------Halls of residence----------------------
Each hall of residence has a name, address, telephone number, and a hall manager who supervises the operation of the hall. The halls provide only single rooms, which have a room number, place number, and monthly rent rate.
The place number uniquely identifies each room in all halls controlled by the Accommodation Office and is used when renting a room to a student.

-----------------------------------Student flats-----------------------
The Accommodation office also offers student flats. These flats are fully furnished and provide single-room accommodation for group of three, four, or five students. The information held on student flats includes a flat number, address, and the number of single bedrooms available in each flat. The flat number uniquely identifies each flat.
Each bedroom in a flat has a monthly rent rate, room number, and a place number.
The place number uniquely identifies each room available in all student flats and is used when renting a room to a student.

A student may rent a room in a hall for various periods of time. New lease agreements are negotiated at the start of each academic year with a minimum rental period of one semester and a maximum rental period of one year, which includes Semesters 1, 2, and the Summer Semester. Each individual lease agreement between a student and the Accommodation Office is uniquely identified using a lease number.
The data stored on each lease includes the lease number, duration of the lease (given as semesters), name and matriculation number of the student, place number, room number, address details of the hall, and the date the student wishes to enter the room, and the date the student wishes to leave the room (if known).

At the start of each semester each student is sent an invoice for the following rental period. Each invoice has a unique invoice number.
The data stored on each invoice includes the invoice number, lease number, semester, payment due, student's full name and matriculation number, place number, room number, and the address of the hall or flat. Additional data is also held on the payment of the invoice and includes the date the invoice was paid, the method of payment (cheque, cash, visa, etc.), and the date the first and second reminder is sent (if necessary).

--------------------Student flat inspections--------------------
Student flats are inspected by staff on a regular basis to ensure that the accommodation is well maintained. The information recorded for each inspection is the name of the member of staff who carried out the inspection, the date of inspection, an indication of whether the property was found to be in a satisfactory condition (yes or no), and any additional comments.

---------------------Accommodation staff-------------------------------
Some information is also held on members of staff of the Accommodation Office and includes the staff number, name (first and last name), home address (street, city, and postcode), date of birth, sex, position (e.g. Hall Manager / Administrative Assistant / Cleaner) and location (e.g. Accommodation Office or Hall).
The accommodation Office also stores a limited amount of information on the courses run by the University including the course number, course title (including year), course leader, internal telephone number, room number, and department number. Each student is associated with a single course.

Whenever possible, information on a student?s next-of-kin is sorted which includes the name, relationship, address (street, city, postcode), and contact telephone number.

According to this case study you have to do the following thing:-


Based on the particular case study,

1.Provide a user?s requirements specification for the case study.


3.Create and validate a logical data model

4.List THREE (3) potential threats that could occur and propose countermeasures to overcome them.

5.Based on the logical data model developed in question 2, create a physical database design

6.Implement the database
a.CREATE TABLE statement
b.ALTER table, add TWO(2) new column
c.CREATE TWO(2) view (should combine TWO or MORE table)
d.DROP view
e.GRANT privileges
f.REVOKE privileges
g.INSERT new data
h.UPDATE data
i.DELETE data
j.Answer EVERY query transactions (sample) in the case study.
k.Provide the screen shot for all the output (6(a) until 6 (j)


View Answers

Related Tutorials/Questions & Answers:
Isolation level is used by the DBMS.
Isolation level is used by the DBMS.  What isolation level is used by the DBMS when inserting, updating and selecting rows from a database
Advanced DBMS questions
Advanced DBMS questions   Five company/organization that involves in SQL Server database migration. what is the Transaction Recovery Method. What does Cloud Computing contributes in the RDBMS? Importance
the isolation levels supported by my DBMS
the isolation levels supported by my DBMS  How can I determine the isolation levels supported by my DBMS
Ask DBMS Questions online
Ask DBMS Questions online       DBMS stands for Data Base Management System (DBMS) is a set of computer programs that controls the creation, organization
dbms_change_notification new_reg_start
dbms_change_notification new_reg_start  Please explain it "dbmschangenotification newregstart" Thanks in advance
use JDBC to execute non-standard features that my DBMS provides.
use JDBC to execute non-standard features that my DBMS provides.  Can I use JDBC to execute non-standard features that my DBMS provides
Database Management System (DBMS)
Database Management System (DBMS) A Database Management System (DBMS) sometimes called a database manager or database system is a set... of the data. A DBMS is a system software package that ensures the integrity
Open Source DBMS
Open Source DBMS Open Source Database Management Systems The rapid... of its adopters.    Open source DBMS vendors Like other... evaluating several database software vendors, Holden chose MySQL DBMS (database
Database Connectivity Example In Java
that converts Java program requests to a protocol into which the DBMS can... will try to create a connection with the DBMS and then we will write the SQL... DBMS to make the connection between Java program and DBMS to access database
pls answer - Security
pls answer  what are the difference of open source dbms and proprietary dbms
SQL Server
SQL Server  Hii, What is SQL Server ?   hello, SQL Server is a DBMS system provided by Microsoft
A connection pool with multiple user ID.
A connection pool with multiple user ID.  Can I set up a connection pool with multiple user IDs? The single ID we are forced to use causes problems when debugging the DBMS
Class.forName in jsp
a connection with a DBMS
Collection  actually why do we need collections? means we can also store group of objects in relational data base and dbms also provides all the operatoins insert,delete,update,sort,search etc. then why collection
Java - JDBC
databases.  Difference between DBMS and RDBMS A DBMS is persistent... to exist or even the application that created the data restarted. A DBMS also... relationships between tables. DBMS does not impose any constraints or security
What is a database?
System (DBMS). 2 Relational Database Management System (RDBMS). 1. DBMS : DBMS stands for database management system and it includes theoretical part... can access the database at the given time in DBMS.   In DBMS, you can
jdbc interview question
. It translates JDBC calls to a DBMS-independent network protocol, which is then translated to a DBMS protocol by a server. Flexible. Pure Java and no native code. Type 4... machine to the DBMS server. It doesn?t need any special native code on the client
Java Spring Hibernate Struts Training How to Fetch Data using dropdown option How to get day from date in SQL? Example of sorting results in MySQL Distinct and Limit example in MySQL How to download install and use MySQL workbench? Example of Average, Sum, Min and Max functions in MySQL How to delete data from MySQL? Update SQL Query Example Order by example in MySQL AND and OR Operators in MySQL Query Where clause with query in MySQL How to use select Statement in MySQL? How to insert data into MySQL Table? Create Database and tables in MySQL example of before insert trigger in mysql Failed to Call Procedure MY Sql Query Error - Not able to Understand the issue which query will be run for previous(button) question How to access data yearly from DB in C# ? Fastest way to upload huge raw data in database - c#, Java or stored procedure DB Insert Nb hours between 2 time stamps meets specific criteria mysql select into table from another table example MySQLselect into new table Example code How to create a .mdf file from script (.sql) Please help me out fixing broken relational links in MySQL database after an upgrade How do I upgrade mysql? mysql 4.x to 5.x upgrade problem Upgrading a PHP/MYSQL application? upgrading sql query join phpmyadmin in whm whm php version update php cpanel cpanel mysql downgrade cpanel mysql version upgrade mysql whm upgrade mysql 4 5 cpanel upgrade mysql cpanel server Did my upgrade from mySQL 5.1 to 5.5 work Upgrade to MySQL 5.5 ? - cPanel Mem use upgrade from MySQL 5.1 to 5.5 - cPanel Upgrading MySQL from 5.0 to 5.1 or 5.5 - cPanel Mysql Upgrade on WHM (Mysql 5.1 to 5.5)cPanel Mysql Upgrade on WHM (Mysql 5.1 to 5.5) - cPanel Updating from MySQL 5.0 to 5.5 cPanel MySQL 5.1 upgrade to 5.5 error Charset problem upon publishing PHP + MySQL website MySQL issue: Table 'data_dictionary.CHARACTER_SETS' doesn't exist SQL Error Version 5.5.15


Advertisement null