ABC University's Database System: Data Validation & Effectiveness - Prof. Pantheris, Study notes of Network and System Administration

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% November 2020 Submission Date 8th December 2020 IV Name & Date Mr. Gajhanan V. 6/11/2020 Learner Declaration I certify that the work submitted for this assignment is my own and research sources are fully acknowledged. 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. 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 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. Learning Outcomes and Assessment Criteria Pass Merit Distinction LO1 Use an appropriate design tool to design a relational database system for a substantial problem P1 Design a relational database system using appropriate design tools and techniques, containing at least four interrelated tables, with clear statements of user and system requirements. LO2 Develop a fully functional M1 Produce a comprehensive design for a fully functional system which includes interface and output designs, data validations and data normalization. relational database system, based on an existing system design P2 Develop the database system with evidence of user interface, output and data validations, and querying across multiple tables. P3 Implement a query language into the relational database system. M2 Implement a fully functional database system which includes system security and database maintenance. M3 Assess whether meaningful data has been extracted through the use of query tools to produce appropriate management information. LO3 Test the systems against user and system requirements P4 Test the system against user and system requirements. M4 Assess the effectiveness of the testing, including an explanation of the choice of test data used. D1 Assess the effectiveness of the design in relation to user and system requirements. Lo2&3 D2 Evaluate the effectiveness of the database solution in relation to user and system requirements, and suggest improvements. LO4 Produce technical and user documentation 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. 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 Campus_ID Unique id of each divided into campuses 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 Faculty_ID ID of the faculty which campuses 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 Supervisor_ID Unique id of each campuses supervisor Supervisor_Name Name of the supervisor Committee Faculty have Committee_ID Committee ID committees 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 10 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 Lecture_Id Unique id of each students lecturer Lecture_Name Name of the lecturer Lecture_Title Title of the lecturer Office_Room Room number Course Courses offered by the | Course_ID Unique ID of each programme course Course_Name Unique name of each courses Student A student of the ABC Student_ID Unique id of each university 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 | P_Code Unique code to school represent each programme Unique_Code Unique code for each programme Programme_Title Title of the programmes Programme_Level Level of the programmes 11 1.1.2 Interrelated Table Task 1 1.1 Interrelated Table Campus Faculty Committee Supervisor Campus_ID Faculty_ID Committee_ID Supervisor_ID Campus Name ] located 1 Faculty_Name 1 have N Committee_Name Supervisor _Name Campus_Address Faculty_Dean Distance Located_Building 1 1 N has 1 charged IL located devided N N Club School Lecturer Course Club_ID Nlooranl D+ Club_Name School_ID 1 employers w_ | Lecture ID N teachs -M | Course_ID Buildin: School_Name Lecture_Name Course_Title € Located Building Lecture Title Phone_Number _ Office_Room Sports = N 1 teaches M N have ood M 3 Ll Student N Programme Student_ID P_Code Student_Name enrollment Unique_Code S_Birthday , N Programme Title ‘Year_Of Recruitment 1 | Programme Level offered N 12 2>¢ Normal Form A table is said to be in 2NF if both the following conditions hold: e Table is in INF (First normal form) ¢ No non-prime attribute is dependent on the proper subset of any candidate key of table. An attribute that is not part of any candidate key is known as non-prime attribute. 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) 3™ Normal Form A table design is said to be in 3NF if both the following conditions hold: e Table must be in 2NF ¢ Transitive functional dependency of non-prime attribute on any super key should be removed. 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) 15 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 e 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. 16 Lookup This helps to lessen errors in a field with a limited list of values. For example, the fact that there are only 12 possible months in a year ensures that the list of possible values is limited. Advantages of a lookup list are as follows: Faster data entry—because it is typically much faster to select from a list than to type each individual entry. Enhanced accuracy—because it lessens the risk of spelling mistakes. 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. 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 wont be able to proceed to the next step, nor will they be able to save any other data which they have entered. 17 02 - Home Page comps UNIVERS Lrecutty.| ITU USER ACCOUNT Committee . hav ee am anew use |supenvisor Welcome back. Please og n a aa orget your password 2 User Account _Suudent | 20 03 - Campus Campus ID Campus Name Campus Address Distance 04 - Faculty Faculty ID Faculty Name Faculty Dean Located Building 21 05 — Supervisor Supervisor ID Supervisor Name 06 - Club Club ID Club Name Location of Phone Number Sports 22 11 - Programme Programme Code Unique Code Programme Title Programme Level 25 2.1 Querying across multiple tables. 01- Campus /$0UQuerydsq-LAPTOP-HGTTEE | ee sable Campus ( Campus_1D varchar (20) prinary key nt null, Campas_Kent_ vatehar (15) not null, Caupus Address vazcher|§0) not moll, Distance varchaz (1), be _/ SQLQuery3.sal - LAPTOP-LH(9773E..| elect from Campus; Sinsext dave Canpus (Campus_ID, Campus Nene_, Campus Actress, Distance) '¢_16535", "Campus A','78 Randkzishne Road Cclomba', "Three Mu!) ; "canpus_B','72 Pitzkette Rrad Colewbo!, "Iwo Ba"): angus Canpus_1D,Cempus Newe_, Campus _Aécress, Distance) Values |*2 16537", "Campus_C', "123 Athulkott= Road Celombo", "Four Kn"); > Fests y Vesans Campus ID Campus Name_ Campus_Adcress Dsarce 1 C1655 7 Campus A 73 Ranzkisrna Road Colorbo Three Kor 2 016536 Campus B 72 Fracotte Roac Coombe Two Kn 3 ‘Campus_C 123 Ahdkottz Road Cobbs = Four Kn 4 Campusl ‘$3 Raaciiye Road Coombs ve Km 26 02 - Club. “SOU Quer.sql- [APTOP-LACOTTEF. x te table Club i \ Club_ID varchar(15) primary key not cull, Club Name varchar(18) net null, Building vazcher (15) n2z mull, Paone Maker int, Sports varchar (53) Ve _S0uery3sq\-LAPTOP-LHCSTTBF.) yx ‘ select ‘Z1oa Club; inser: into Club (Club ID,Club Kame, Building, Phone Nunber, Sports) (*CRab_A', "Campug_A_Clud", '6th building! , 07122366567, 'Fo2cball, Cricke:,Retoall'): {int Club |Club_ID,Club Kame, Suilding, Phone Nunber, Sports) (‘Chak B', "Campus 2 Clu', "1st building’, 07876456, 'Footzall, Cricket, Rugby"): into Club|Club_1D,Club Kame, Building, Phone Nunber, Sgort) Values('Clut_¢', "Campus_¢ Clu", '2nd building’ , 073567654, 'Fostbal1, Cricket Rugby"): insert into Club Club ID, Club Name, Building, Fhont Hunber, Sports| Valuss ('Clak_D', ‘Campus 0 Club", "Ist building’, 0714088622, 'Foz<ball,Crick2:,Netball') ’ « > a Pets 7 Messages OueO Skb Nene Euldng Fore Naber Stoke 1 {Clb A” Corpus 8 Cus Shbatrg 71223557 Fool ike Nba 2 Chb_B Corpus 3. (xb “sbuling 77EHE6 Fete, ihe: oy 3 ‘ncbuldng THEBTESL Foal ike Pay 4 “sbuling 7140322 Footel ice etal 27 05 - School due sq -L-HGTT3Awser 63) Create cable School ( School_ID vazchar|10) prinazy key not null, School Nané varchar (20) n0t nL, Located Building varchar (20) SQL Query3sql- LAPTOP-LHCOTTSF.. select! fzon School = insert into School (Schcol_I0,Schcol_Name, Located Building) -values(*16532', ‘School of Physics’, "1st building’); = insert into School (Schcol_I0,Schcol_ Name, Located Building) “values "26425', School of Cheniszzy’, "3rd building") +X >Lx Fendt Hee School_I2 Schcol_Name Located_Buikirg 2 BA schol Charity adbuidng 30 06 - Supervisor Wen LAST) +X Creace table Supervisor ( Supervisce_I0 vercher(10) primary key not aull, fuperviscr Nae vaxctar|20) i | SQL Qvery3.sql- LAPTOPARCOTTAF..| selecz!from Supervisor imserz into Supervisor (Supervisor_ID, Supervisez Mane! -values('js_0138", 'G.3.S.Arncld") « rents By Voss Sipeniso|D _ Supenis Name 1 FR gesanid 31 07 — Lecturer “SQduesq-L HGTT3Fser 53) jCreaze table Lecvurer ( Lecturer_ID varchar (10) prinary key not mull, lecturer Nene varchar (18) n2> null, lecturer Title vazchar (20), Office Room varchar (15) i -$0LQuery3.sql- LAPTOP-LHC9773F... select! from Leczuzer insert inco Leccuzer |Lecturer_ID,Leczurer Kame, Lecturer Title, 02ficz Roon) values |"Lec_02","MR.Laimal', "Chem end Physics! , "Roam NO 6") Hy esas [) Wesseges ecu Lecue: Mane Lecture Tile fice Room 1 Leo WRLskna Chen and Pryses | Foon NOE 32 10 - Course SQUGvey-L HOTA er 53) XxX A HCreate tatle Course i Course ID varchar (16) primary key not ml, Course Ticle int ) SQL Query3.sl- LAPTOP-LHCOTI3F.. x select? fram Course [Jinsert into Course (Course_ID,Course Title) Values /*Cours= 211", 'Certificete Level"); Djinsert into Course (Course_ID,Course Title) Values *Cours= 212", "Higher Diploma"); slinsert into Czurse (Courst_ID,Caurse_Title) Values |*Cours= 215", "Higher Netional Diploma’) GO Pesuts (5) Messages Couse |. Couse Tee 1 “Cause 217” cetfea Level 3 Couse 215 Fighe Noval pana 35 2.1.2 Implement the data validation 01 — Type Check (Add data type) Ser s9- LAPTOP HCO E create cable Club Chub_BD vezchar(25) primary key sot aul, Club Bane verchar (18 Building varchar(15 Fone aber Sct @] Sperea varchar (60 02 — Length Check (Add size for the data type) >ulx SOL due -LAPTOPLHCGTTE Create table Campas Disrance varchar 0) 03 — Presence Check (Null or Not Null) >i Ove LCST 63) pCreace table Faculty Facalty ID rarenar (15) primary 1 Taoalty Name varchar! Feoaliy Dean varchar! eeazed Balieing vazcnar (20) 1 36 2.1.3 Data Validation ¢ Data validation is a method for checking the accuracy and quality of your data, typically performed prior to importing and processing. It can also be considered a form of data cleansing. Data validation ensures that your data is complete (no blank or null values), unique (contains distinct values that are not duplicated), and the range of values is consistent with what you expect. Often, data validation is used as a part of processes such as ETL (Extract, Transform, and Load) where you move data from a source database to a target data warehouse so that you can join it with other data for analysis. Data validation helps ensure that when you perform analysis, your results are accurate. Steps to data validation Step 1: Determine data sample Determine the data to sample. If you have a large volume of data, you will probably want to validate a sample of your data rather than the entire set. You’ll need to decide what volume of data to sample, and what error rate is acceptable to ensure the success of your project. Step 2: Validate the database Before you move your data, you need to ensure that all the required data is present in your existing database. Determine the number of records and unique IDs, and compare the source and target data fields. Step 3: Validate the data format Determine the overall health of the data and the changes that will be required of the source data to match the schema in the target. Then search for incongruent or incomplete counts, duplicate data, incorrect formats, and null field values. 37 Develop and Rigidly Adhere to Data Standards Simply put, this means that if the information you store in your database is not entered accurately or uniformly, no amount of reporting, consulting, or crying is going to give you the information you need. This is where data standards come into play. At one university spent many months working with key database users developing a set of standards by which to enter all data. Not only do the data standards state explicitly how to enter data but it also clearly explains where to enter data and what fields must absolutely be entered in order for a record to be complete. Standardizing data entry is critical for developing useful and accurate management reports. Without strict standards, sorting of data can become difficult if not impossible. Know What You Need and Want from Your Database Too often universities select an association database system based on what it is capable of doing, rather than on whether it does what the association needs it to do. Understanding what you need from your database is not only key in the selection of your database but is essential to its continued effectiveness. Typically there are several functions that a database needs to provide: contact management (address, phone, fax, email), membership tracking and invoicing, volunteer/committee activity, and more. But what are some of the other items you need to track now or may need to track in the future? How about meeting attendance, publications purchasing history, continuing education credits, or areas of interests? All of these issues require the collection of data. If you’re not collecting it, you can’t report it out, and you have to know you want to report it out to begin collecting it. Understanding what your needs are now, and anticipating what your needs may be in the future can go a long way toward creating an effective database. 40 3.2 System Requirement System requirements are the required specifications a device must have in order to use certain hardware or software. For example, a computer may require a specific I/O port to work with a peripheral device. A smartphone may need a specific operating system to run a particular app. Before purchasing a software program or hardware device, you can check the system requirements to make sure the product is compatible with your system. Typical system requirements for a software program include: © Operating system ¢ Minimum CPU or processor speed ¢ Minimum GPU or video memory ¢ Minimum system memory (RAM) ¢ Minimum free storage space e Audio hardware (sound card, speakers, etc) System requirements listed for a hardware device may include: © Operating system e Available ports (USB, Ethernet, etc) ¢ Wireless connectivity ¢ Minimum GPU (for displays and graphics hardware) Functional Requirements Functional requirements should include functions performed by specific screens, outlines of work-flows performed by the system, and other business or compliance requirements the system must meet. Download an example functional requirements specification or use these quick examples below. Interface requirements e Field 1 accepts numeric data entry. e Field 2 only accepts dates before the current date. ¢ Screen | can print on-screen data to the printer. Business Requirements ¢ Data must be entered before a request can be approved. ¢ Clicking the Approve button moves the request to the Approval Workflow. ¢ All personnel using the system will be trained according to internal SOP AA-101. 41 Regulatory/Compliance Requirements e The database will have a functional audit trail. ¢ The system will limit access to authorized users. ¢ The spreadsheet can secure data with electronic signatures. Security Requirements ¢ Members of the Data Entry group can enter requests but can not approve or delete requests. ¢ Members of the Managers group can enter or approve a request but can not delete reque: ¢ Members of the Administrators group cannot enter or approve requests but can delete requests. Depending on the system being described, different categories of requirements are appropriate. System Owners, Key End-Users, Developers, Engineers, and Quality Assurance should all participate in the requirement gathering process, as appropriate to the system. Requirements outlined in the Functional Requirements Specification are usually tested in the Operational 42