1. Background You have been approached by University X and asked to provide a design for a database system that will store information on the programmes, faculties, staff, student and course information. An initial requirements collection and analysis phase of the database design process has been completed. The following (very incomplete and in some situations incorrect) project specifications for the university is given in section 2 below. The proposed database is developed in various modules, including requirements analysis, conceptual database design, logical database design, and physical database design. In assignment 1, you are required to develop user requirements specification and the conceptual database model for University X. Your lecturer will discuss the details of the requirements in class as well as act as the client of the system. You need to implement all the details mentioned in the lecture as well as described in this document. You are encouraged to ask questions to the lecturer to clarify requirements. 2. Main Features and Business Requirements University X has similar requirements to UoN. There are a number of organisational units which make up the university. Each organisation unit has a unique code, a unique name, an abbreviated name, a description, a title for the head of the unit (such as ‘Head of School’, ‘Pro Vice-Chancellor’) and a main contact number. Examples of organisational units include – Academic Division, Research Division, Faculty of Engineering and Built Environment, Faculty of Business and Law, Centre for Teaching and Learning, etc. Some units may have sub-units (for example, Faculty of Engineering and Built Environment may contain School of Electrical Engineering and Computing). A number of staff members work at the university (both academic and administration staff). Each staff member has a unique staff number, name, address, email and contact number. Each member of staff is associated with at least one unit in the university. It is possible for staff to move to different units. Hence, there is a start date, end date Page 2 of 4 and the role played (e.g. ‘Lecturer’, ‘PVC’, etc.) in the unit that needs to be maintained for an association between a staff member and a unit. Each unit has a current head/manager for the unit who is a staff member. There a number of different academic programmes offered by certain units in the university (e.g. “Bachelor of Information Technology”, “Bachelor of Computer Science”, etc. offered by the School of Electrical Engineering and Computing). A programme has a programme code (unique), a descriptive name (unique), total credits to complete, level (Certificate, Bachelor, Masters, PhD) and certification achieved (e.g. Dip, BSc, MSc, PhD etc.). The university has a number of campuses physically located in different parts of the country and overseas. Each campus has a campus id, a name, suburb or city where it is located and a country. There are a number of facilities that belong to each campus (e.g. class rooms, office rooms for staff, laboratories etc.). Each facility has an id (which is unique for the campus), room number, building name, a capacity and the type of the facility (room, classroom, laboratory etc.) Each year, the university has a number of time periods called semesters and trimesters where subjects are offered. Each semester/trimester has a unique id to identify it, a semester or trimester name, and a year. A number of courses are offered by the university. A subject has a course id (unique), course name (unique), number of credits assigned for the subject, and a brief description. Each course belongs to at least one academic programme. A course may be assigned to multiple academic programmes. You need to maintain the start and end date for each course assignment to the academic programme. Also, the courses assigned to the academic program should specify the role the course plays in the academic program (e.g. core course, compulsory course etc.). Some courses have other courses as assumed knowledge. When a course is offered, it is termed a “course offering”. A course offering contains a single subject, a semester/trimester it is offered and a campus it is offered in. A staff member coordinates a course offering, called the Course Coordinator. Each course offering has a number of timeslots in which students and lecturer/tutor meets in different facilities (classroom, lab etc.) for particular activities (e.g. lecture, lab, tutorial, workshop etc.). The timetable information needs to be maintained in the database. There are a number of students who are enrolled in the university. A student enrols in a particular academic programme in a particular semester/trimester. This is also known as “student enrolments”. The date a student enrols in an academic programme must be maintained. After enrolment, a student may register to a number of course offerings. In each course offering, the student will register for particular activities in different timeslots Page 3 of 4 (e.g. a particular lab session and a particular lab session). The database needs to maintain the date enrolled in the course offering and the final mark and final grade obtained by the student when the student completes the course. 3. Assignment (individual assignment) The proposed database system is developed in various modules, including requirements analysis, conceptual database design, logical database design, and physical database design. In this assignment 1, you are required to complete the first two stages of the database design, i.e., to develop user requirements specification and the conceptual database model for the database based on the business requirements provided in this document. There are two parts to be completed in assignment 1 as described below. Part 1: Requirements In this assignment, you are required to develop a user requirements specification truly fulfilling the data requirements (identify what types of data needs to be stored in the database), transaction requirements (identify the important and frequent database operations – data manipulation and queries), and business rules for the University X database mentioned above. Assignment submission format for the Requirements part: The requirements document MUST have the following sections: • Data Requirements – outlining the major data items • Transaction requirements – outlining the data manipulation and queries • Business Rules Hint: Sample requirements documents are discussed in weeks 2 and 3, and are available in appendices A and B of your main textbook. You may interview your client (i.e. lecturer) for clarification and include your interview questions and responses Part 2: EER Model with data dictionary Draw an EER model for the requirements identified in Part 1. The EER Model must be shown in UML notation which is discussed in class and provided in our text. The EER Model should be accompanied with a data dictionary which includes entity type table, relationship type table and attribute table. Assignment submission format for the EER Model with Data Dictionary part: The requirements document MUST have the following sections: • EER Model • Documentation – Data dictionary details (description of entities, relationships and attributes) Sample format for documenting the data dictionary is provided below. Page 4 of 4 Data Dictionary Format: Use the format described in your main text in documenting the data dictionary. Following provides samples for reference. ENTITY TYPES Entity Name Description Aliases Occurrence Collection A collection is a physical collection of items in the library located at a particular physical location Physical area of the library is divided into a set of collections … RELATIONSHIP TYPES Entity name Multiplicity Relationship Multiplicity Entity name PhysicalCopy 0..* LocatedIn 1..1 Collection … ATTRIBUTES Entity Name Attributes Description Data Type & Length Nulls Multivalued Derived Default Student studentId A unique id given to every student char N N N Hint: Sample EER models and documentation is provided in Chapter 16 of your text. Method of submission: Both softcopy submission and hardcopy submission are required: • zip all required files into one zip file. The file name MUST be identified by 4 sections: A1, your first name, your surname, and your student number, e.g., A1SimonLee1234567.zip • It must be submitted to Blackboard -> Assessment -> Assignment1 -> Assignment1 Submission • Print the document, hand in the hardcopy to the lecturer by the due time. The hard copy must have on the front a signed copy of the cover sheet (Assessment Item Cover Sheet – Individual) which is available from: http://www.newcastle.edu.au/__data/assets/pdf_file/0008/75383/AssessmentIt emCoverSheet.pdf Note: Ten percent of the possible maximum mark for the assessment item will be deducted for each day or part day that the item is late. Weekends count as one day in determining the penalty. Assessment items submitted more than five days after the due date will be awarded zero marks. Note: If your hardcopy submission and softcopy submission are not at the same time, the time of the later submission will be counted as your assignment submission time.