CP5633 Database modelling Assignment 1 ERD Modelling Page 1 of 6 CP5633 Database Modelling Assignment 1- ERD Modelling Assignment worth: 20% Due date: study break, Sunday 23rd April 2017 @midnight Note: This is an individual assignment. While it is expected that students will discuss their ideas with one another, students need to be aware of their responsibilities in ensuring that they do not deliberately or inadvertently plagiarize the work of others. Rationale This assignment has been designed to assess your ability to model data, by constructing an entity-relationship diagram for a particular business scenario. This assignment addresses the following learning objectives for this subject: Perform conceptual modelling using Entity-Relationship Diagrams (ERDs). Discuss business assumptions which affect data modelling. Model a database using the entity-relationship (ER) model and understand the database development process. Use the techniques of normalization. Tasks 1. You are to write a brief discussion of your solution, i.e. how you approached the modelling problem and any issues you may have encountered (maximum of ½ page). 2. You are to write all applicable business rules necessary to establish entities, relationships, optionalities, connectivities, cardinalities and constraints. If a many–to-many relationship is involved, state the business rules regarding the bridging entities after breaking down the many-to-many relationship. An example business rules format can be found in the Appendix of this document. Business rules you write are expected to be in the same format as presented in the Appendix. 3. Based on these business rules,* draw a fully labelled and implementable Entity-Relationship Diagram (ERD). Include all entities, relationships, optionalities, connectivities, cardinalities and constraints. You must use Crow’s foot notation and MS Visio to create the ERD (a hand-drawn ERD will NOT be accepted). A sample ERD can be found in the Appendix of this document. (Note: The ERD created using Visio will need to be saved as an image file and then be included in your document file to be submitted). CP5633 Database modelling Assignment 1 ERD Modelling Page 2 of 6 4. A summary to describe the major justifications, assumptions and limitations related to your database design. For example: a. Assumption/justifications for optionalitiy, connectivities, constraints data type and data domain; and b. Special cases or data integrity issues that cannot be handled. Note: Designing the database is an iterative process; you may find yourself going back and forth between Tasks 1 and 3 to revise the design. Make sure that your final submission shows consistent design in the business rules and ERD. Submission You need to submit a document file (MS Word format) to LearnJCU; this document should include all the answers for tasks 1-4. Please name the file as LastnameFirstnameA1.doc. You need to also submit the MS Visio file containing the ERD. Timestamp shown on LearnJCU assignment submission will be used to determine if the assignment is late or not. Refer to the subject guide for the policy for late submission. CP5633 Database modelling Assignment 1 ERD Modelling Page 3 of 6 Business Description ABC Education (ABCE) is a private company which owns a number of language/vocational branch colleges in Sydney, Newcastle, Brisbane, Gold Coast, Townsville and Cairns. Each college offers a wide range of English language courses as well as vocational courses from Certificate level up to Advanced Diplomas in a variety of disciplines. In order to meet different local specific conditions or demands each branch college may offer different range of courses. Each college is managed by a college manager who was hired and allocated by ABCE Head Office. As college managers they have authority to manage any college-related business matters including student enrolment managements, human-resource management, financial/accounting matters, maintenance of college buildings, etc.. Each college manager is expected to send a report to the ABCE Head office in Brisbane periodically or whenever requested. This report summarizes the total revenues in fees collected, student enrolment summary, total expenses in maintenance and staff salary, staff details, courses offered etc.. Currently managers fill out a paper form and mail it back to head office because the computer system is established for each college but is not integrated for the whole ABCE Company. Many college managers have complained that preparing this report is a very difficult and time consuming process. Also, the managers at the head-office also have expressed concerns about the accuracy and verifiability of the reports. To reduce these concerns and to improve the ease and efficiency with which the college managers and employees conduct their daily business, the company is proposing to development a centralized database that can be used by the managers to track the daily business of their colleges and to prepare their reports. You have been asked to design a database that satisfies many user requirements provided by ABCE. General business description and various user requirements are summarized here: Each College is organized by department (English Language, Information Technology, Business, and so on). There are two types of staff member working for the college; administrative staff and faculty (academic) member. Each academic member is assigned to a single department but most administrative staff work for overall college level (not for specific department level). All employees in College are working in an office allocated and some offices are shared by multiple people in the college. Every staff employed by a college of ABCE is identified as a unique ABCE staff number and ABCE allows a staff to be re-located from college to college if the staff wish and/or re-staffing is needed accordingly. The staff number is never changed even in this case. Most departments offer more than one course; for example, the Information Technology department might offer courses in Computing/Networking, Interactive Game Design, and Business Informatics. Each course, however, is offered by only one department. Students can enroll more than one course at the same time, but most students are enrolled for only one. Each CP5633 Database modelling Assignment 1 ERD Modelling Page 4 of 6 student is assigned a faculty member as an advisor for his or her study; students who do more than one courses are assigned a faculty advisor for each course. One academic member should not be an advisor for more than 30 students at the same time. A code that has up to three characters (IT for Information Technology, EL for English Language, BU for Business, and so on) identifies each department. A department runs a number of subjects and each subject is identified by the combination of the department code and a three-digit number. The number of credits offered by a particular subject does not vary; that is, all students who pass the same subject receive the same amount of credit. Every subject has at least one text book required and up to 3 supplementary texts. The department secretary needs to make a list of all subjects run in the department and textbooks required for each subject to make an order to the bookshop. A seven-character code identifies the study-period (some colleges run semesters but others run trimesters) in which a subject is taught in a specific year (for example, SPT12015 represents the trimester 1 of 2015). ABCE’s each college sets all necessary schedules for each study period including the date the study period begins and ends, the date final exams begins and end, and the last withdrawal date. For a given study period, a department can run multiple classes of a same subject and allocates a different time slot for each class. Each class can be taught by a different faculty (academic member) in the department. The details of every class for all available subjects (subject number/title, class code/name, weekly time schedule, venue, the number of credits generated by the subject, teacher name, maximum enrolment permitted to the class, and the prerequisites for the subject) are provided to students before each semester commences and students use them to indicate the classes in which they want to enrol. If a student fails the subject in a semester (or trimester), the student may take the subject again later (in another semester or trimester). Before making the enrolment process for a semester or trimester, every student has to submit a registration request form to request classes for the upcoming semester (or trimester). Students indicate the classes for which they want to register by entering each class’s code. For each of these classes, students may also enter a code for an alternative class in case the first requested class is full. After all students have been assigned to classes, the system produces a student schedule form, which is mailed to students so that they know the classes in which they have been enrolled. This form shows the schedule for an individual student for the indicated semester. After all students have completed the enrolment process for a given semester (or trimester), each faculty member receives a class list for each class he/she will be teaching. In addition to list the students (student number and name) in each class and general details of the class (department name, subject name, class code/name, semester, credits, weekly class time, days and venue), the class list provides space to record the grade each students earns in the subject. At the end of the semester (or trimester), the faculty member enters the students’ grades in this CP5633 Database modelling Assignment 1 ERD Modelling Page 5 of 6 list and sends a copy of the list to the records office, where the grades are entered into the database. After a staff of the records office posts the grades (by entering them into the database), the DBMS generates a report card for each student. The report card shows the list of subjects the students took during the semester. For each subject taken, the following details are reported; subject name, department name of the subject, subject title, grade, credits earned and grade points. The report also shows the summary of the current semester totals and cumulative totals including credits earned, GPA, Total Points, and so on. The report card also contains the student’s permanent address and a local (alternative) address (if different). Then the report cards are mailed to the addresses printed on the report card. The grades earned by a student become part of his/her permanent record and will appear on the student’s transcript. Students can request to get his/her “Full Academic Records” at any time (before or after completing his/her college course). This report lists complete information about a student; including his/her course(s) undertaken, department, academic advisor(s), all grades/credits received to date. In some situations, employees at ABCE (head office or any college) may require reports to manage information about faculty members. This report lists all faculty members by college / department and contains each faculty member’s ID number, name, address, office location, phone number, current rank (Associate Lecturer, Lecturer, Senior lecturer, Associate Professor, or Professor), and starting date of employment. It also lists the number, name, local / permanent addresses of each faculty member’s advisees. It also shows the employment history of specific faculty members. Employees at ABCE also may need to generate a report about a specific subject, a specific department or a specific college. A subject report (for each subject) lists the code and name of the department that is offering the subject, the subject number, the description of the subject, the number of credits awarded, and the department and subject number for each prerequisite subject. Similarly, a department report (for each department) lists details of a department including name, location, a head of department (one faculty member is selected as a head of department), name/office/phone number of each academic member of the department, the location of the department office, phone number, etc. A college report lists relevant information in a similar way. ABCE has a supply of computers (PCs or laptops) or other equipment that it hires out to faculty members or students for a fee which varies from item to item. Most staff or students use their own PCs or laptops but not all faculty members or students have their own machine. The secretary in the hire office needs to generate reports to show for each machine owned by the College, the item number, description, purchase date and price and, if it is on hire, the name of the faculty member or the student who is currently hiring it. A department may need to request a CP5633 Database modelling Assignment 1 ERD Modelling Page 6 of 6 report showing the list of faculty members or students in the department who hires any college equipment and the details of the equipment hired by them. APPENDIX: SAMPLE BUSINESS RULES & ERD Sample Business Rules: Entity: SCHOOL Each SCHOOL has at least ten or many STUDENTs. Each SCHOOL has one or many SUBJECT. Entity: STUDENT Each STUDENT can take many SUBJECTs. Each STUDENT has one or many ENROLMENT record. Each ENROLMENT record is related to one STUDENT. Each STUDENT is associated with one SCHOOL. A STUDENT is resident of one CITY. Entity: SUBJECT Each SUBJECT is offered by one SCHOOL. Each SUBJECT has zero or many STUDENTs. Each SUBJECT has zero or many ENROLMENT records. Each ENROLMENT record is related to one SUBJECT. Entity: CITY Each CITY has zero or many STUDENT lived in it. Each CITY is in one COUNTRY. Example ERD Format
PLACE THIS ORDER OR A SIMILAR ORDER WITH BEST NURSING TUTORS TODAY AND GET AN AMAZING DISCOUNT
The post CP5633 Database modelling Assignment 1 ERD Modelling Page 1 of 6 CP5633 Database Modelling Assignment 1- ERD Modelling Assignment worth appeared first on BEST NURSING TUTORS .