Download ABC University's Database System: Data Validation & Effectiveness - Prof. Pantheris and more Study notes Network and System Administration in PDF only on Docsity!
Assignment Brief (RQF)
Higher National Certificate/Diploma in Computing
Student Name/ID Number MG66412_Thamindu_Sharitha_Galappaththi. Unit Number and Title 4: Database Design & Development Academic Year 2020 Unit Tutor Ms. Samudika De Silva Assignment Title ABC University System Issue Date 6 th^ November 2020 Submission Date 8 th^ December 2020 IV Name & Date Mr. Gajhanan V. 6/11/ Learner Declaration I certify that the work submitted for this assignment is my own and research sources are fully acknowledged.
Unit Learning Outcomes LO1 Use an appropriate design tool to design a relational database system for a substantial problem. LO2 Develop a fully functional relational database system, based on an existing system design. LO3 Test the system against user and system requirements. LO4 Produce technical and user documentation. Assignment Brief and Guidance Submission Format The submission is in the form of an individual written report. This should be written in a concise, formal business style using single spacing and font size 12. You are required to make use of headings, paragraphs and subsections as appropriate, and all work must be supported with research and referenced using the Harvard referencing system. Please also provide a bibliography using the Harvard referencing system. The recommended word limit is 2,000 - 2,500 words, although you will not be penalized for exceeding the total word limit. The submission is in the form of a fully functional relational database system and an individual written report. The submission is in the form of a technical documentation and a written report.
different programmes and each programme can be offered by only one school. Each programme has a unique code, title, level and duration. Each programme comprises several courses, different programmes have different courses. Each course has a unique code and course title. Some courses may have one or more prerequisite courses and one course can be the prerequisite course of some other courses. Each of the students is enrolled in a single programme of study which involves a fixed core of courses specific to that programme as well as a number of electives taken from other programmes. Students work on courses and are awarded a grade in any course if he/she passes the course. Otherwise the student has to re-take the failed course. The system needs to record the year and term in which the course was taken and the grade awarded to the student. Every student has a unique ID. The system also keeps the student name, birthday and the year he/she enrolled in the course. The school employs lecturers to teach the students. A lecturer is allowed to work for one school only. Each lecturer is assigned an ID which is unique across the whole university. The system keeps the lecturer’s name, title and the office room. A supervisor maybe in charge of several lecturers, but a lecturer, however reports to only one supervisor. A lecturer can teach many different courses. A course may also have been taught by many different lecturers. The university is operated by committees. Each faculty has to have a number of committees with the same titles across the university, such as the Faculty Executive, the Post Graduate Studies Committee, the Health and Sanity Committee, and so on. The committees meet regularly, such as weekly or monthly. The frequency is determined by the faculty involved. A committee’s members are all lecturers. A lecturer may be a member of several committees. Task 1 Before you start the development process, your manager has asked you to produce a report for the CEO, containing: 1.1 The design of the relational database system using appropriate design tools and techniques. It should contain interrelated tables.
- 2 You will produce a comprehensive design for a fully functional system which will include interface and output designs, data validations and cover data normalization. Clear statements of user and system requirements.
Task 2
2.1 Once the designs have been accepted by your manager you have been asked to develop the database system using evidence of user interface, output and data validations and querying across multiple tables. 2.2 You want to include more than just the basics so you will implement a fully functional database system which will include system security and database maintenance features. 2.3 You have decided to implement a query language into the relational database system. Assessing whether meaningful data has been extracted through the use of query tools to produce appropriate management information. 2 .3.1 List all the schools that are located in a specific area. 2.3.2 List all the programmes provided by 'science faculty'. 2.3.3 List all supervisor's name and the name of the lecturer they manage. 2.3.4 Give all the lecturers with the courses they are teaching. Sort by lecturer name.
Task 3
3.1 Evaluate the effectiveness of the database solution in relation for ABC University environment and system requirements, and suggest improvements. 3.2 Once the system has been developed, you will test the system against user and system Requirements, including an explanation of the choice of test data used. 3.3 Produce technical and user documentation which will be given to the ABC University. Provide some graphical representations for ease of reference in the technical guide and produce a technical and user documentation for a fully functional system, including diagrams showing movement of data through the system, and flowcharts describing how the system works.
P5 Produce technical and user documentation. M5^ Produce technical and user documentation for a fully functional system, including diagrams showing movement of data through the system, and flowcharts describing how the system works. D3 Evaluate the database in terms of improvements needed to ensure the continued effectiveness of the system.
Table of Content
01 Task – 1 1.1 System requirements and tables The following are the identified institutions and affiliates for the proposed database for ABC University. Entity Description of Entity Attribute Description of the Attribute Campus The university is divided into campuses Campus_ID Unique id of each campuses Campus_Name Name of the campus which is unique Campus_Address Address of the campus Distance Representing distance to the city center Faculty Faculties of each campuses Faculty_ID ID of the faculty which is unique Faculty Name Name of the faculty which is unique Faculty_Dean Dean of each faculties Located_Building Located of the faculty building Supervisor Supervisors of the campuses Supervisor_ID Unique id of each supervisor Supervisor_Name Name of the supervisor Committee Faculty have committees Committee_ID Committee ID Committee_Name Name of each Committees Club Campus has many clubs Club_ID Id of the club Club_Name Unique name of the club Building Club building representing
Phone_Number Phone number Sports Name of the sports School Faculty has schools School_ID ID of the school School_Name Name of the school which is unique Located_Building Location of school building Lecturer Lectures who teach students Lecture_Id Unique id of each lecturer Lecture_Name Name of the lecturer Lecture_Title Title of the lecturer Office_Room Room number Course Courses offered by the programme Course_ID Unique ID of each course Course_Name Unique name of each courses Student A student of the ABC university Student_ID Unique id of each student Student_Name Name of the student S_Birthday Birthday of the student Year_of_Recruitment The year student enrolled in programme Programme Programs offered by the school P_Code Unique code to represent each programme Unique_Code Unique code for each programme Programme_Title Title of the programmes Programme_Level Level of the programmes
1.2 Data validations and cover data normalization. 1.2.1 Normalization
- Normalization is a database design technique that reduces data redundancy and eliminates undesirable characteristics like Insertion, Update and Deletion Anomalies. Normalization rules are divided into the following forms.
- First Normal Form
- Second Normal Form
- Third Normal Form The functional dependencies in the current ER diagram are listed below. FD 1: Campus_ID → {Campus_Name, Campus_Address, Distance} FD 2: Faculty_ID → {Faculty_Name, Faculty_Dean, Located_Building} FD 3: Committee_ID → {Committee_Name} FD 4: Supervisor_ID → {Supervisor_Name} FD 5 : Club_ID → {Club_Name, Building, Phone_Number, Sports} FD 6 : School_ID → {School_Name, Located_Building} FD 7 : Lecture_ID → {Lecture_Name, Lecture_Title, Office_Room} FD 8 : Course_ID → {Course_Name} FD 9 : Studenet_ID → {Student_Name, S_Birthday, Year_of_Recruitment} FD 10 : P_Code → {Unique_Code, Programme_Title, Programme_Level}
1.2.3Relational Schema Relational schema refers to the meta-data that describes the structure of data within a certain domain. It is the blueprint of a database that outlines the way its structure organizes data into tables. Campus (Campus_ID, Campus_Name, Campus_AddressDistance) Faculty (Faculty_ID, Faculty_Name, Faculty_Dean, Located_Building) Committee (Committee_ID, Committee_Name) Supervisor (Supervisor_ID, Supervisor_Name) Club ( Club_ID, Club_Name ,Building, Phone_Number, Sports) School (School_ID, School_Name, Located_Building) Lecture (Lecture_ID, Lecture_Name, Lecture_Title, Office_Room) Course (Course_ID, Course_Name) Student (Studenet_ID, Student_Name, S_Birthday, Year_of_Recruitment) Programme (P_Code, Unique_Code, Programme_Title, Programme_Level) 1 st Normal Form As per the rule of first normal form, an attribute of a table cannot hold multiple values. It should hold only atomic values. Campus (Campus_ID, Campus_Name, Campus_AddressDistance) Faculty (Faculty_ID, Faculty_Name, Faculty_Dean, Located_Building) Committee (Committee_ID, Committee_Name) Supervisor (Supervisor_ID, Supervisor_Name) Club (Club_ID, Club_Name, Building, Phone_Number, Sports) School (School_ID, School_Name, Located_Building) Lecture (Lecture_ID, Lecture_Name, Lecture_Title, Office_Room) Course (Course_ID, Course_Name) Student (Studenet_ID, Student_Name, S_Birthday, Year_of_Recruitment) Programme (P_Code, Unique_Code, Programme_Title, Programme_Level)
1.2.4 Data Validation
- Validation is an automated check, performed to guarantee that the data input is rational and acceptable. It does not check the correctness of the data itself.
- Validation is a way of trying to lessen the number of errors in the data input.
- The validation is carried out by the computer when you input data. It is a way of checking the input data against a given set of validation rules.
- The purpose of validation is to make sure any given set of data is logical, rational, complete, and within acceptable limits. Validation Methods There are several validation methods that can be used to check the input data. 01 - Validation Methods
- There are several validation methods that can be used to check the input data. 02 - Range Check
- This is generally used when working with data which contains numbers, currency, or date and time values. 03 - Type Check
- This is a way to confirm that the correct data type is inputted. o For example, in an application form age may range from 0 to 100. A number data type would be an appropriate choice for this data. By defining the data type as number, only numbers are allowed in the field and it would prevent people from inputting verbal data, like ‘eighteen’. o Some data types are capable of doing an extra type check. For example, a date data type will ensure that a date inputted existed at some point in the past, or will exist in the future. It would not, for example, accept the date 30/02/2018. 04 - Check Digit
- This is used to find out if a series of numbers has been keyed correctly. There are many ways to produce check digits. o For example, the ISBN-10 numbering system for books uses ‘Modulo-11’ division, where it outputs the remainder of the division as the result of the operation. Length Check
- This is used to make sure that the correct number of characters are entered into the field. It confirms that the character string entered is neither too short nor too long. o For example, consider a password that needs to be 8 characters long. The length check will ensure that exactly 8 characters are entered into the field.
Lookup
- This helps to lessen errors in a field with a limited list of values. o For example, the fact that there are only 12 possible months in a year ensures that the list of possible values is limited. o Advantages of a lookup list are as follows: o Faster data entry—because it is typically much faster to select from a list than to type each individual entry. o Enhanced accuracy—because it lessens the risk of spelling mistakes. o Greater ease of use—because it limits the options to choose from by only displaying the essential choices. Format Check
- This checks that the input data is in the right format. o For example, a National Insurance number is in the form XX 99 99 99 XX where X is any letter and 9 is any number. Presence Check
- This kind of check makes sure that an essential or required field cannot be left blank: it must be filled in. If someone attempts to leave the field blank, then an error message will be displayed, and they won’t be able to proceed to the next step, nor will they be able to save any other data which they have entered.
Task 2
2. 1 Database system User Interface 01 – User Login
02 - Home Page
