Systems Analysis / Database Design Analysis - Case Study Example

of Institute] of Discipline] ‘Systems analysis database design exercise’ ’ Submission] Introduction The proposed design is that of a computerized information system. This system would be designed so as to accommodate the functionality of keeping a record of the transcripts that are being generated for each respective student. Massive numbers of students get enrolled into a university each year. Each student gets enrolled into a specific degree program and has a number of specific courses assign to him per semester. All these happenings are interrelated. There are a number of things that need to be sorted out and aligned in line with the student’s name if a transcript is to be issued to him. These clauses need to be embedded into the proposed information system if it is to take care of the required generation and issuance of the transcripts. How this handling of clauses is done can be proposed in the text that follows: STAKEHOLDERS and USERS The first thing that is required to be assessed when beginning to design a system is that the stakeholders and the designers of the system should be chalked out. In this particular case, the students enrolled in the university, the instructors teaching courses at the university and the administration of the university would be its stakeholders. The proposed system would be used by the students, the instructors and the system handlers which include the database administrators and the programmers both. Proposed the system would be linked to the accounts management system of the university as well in order to confirm the payment of dues of a student before the issuance of the degree to him. ENTITIES and ATTRIBUTES In order to accommodate the etails of each respective student, the courses beign offered int eh university, the semesters that the university has each year and the details of instructors it is proposed that the following entities be made: Student Instructor Grade Report Grade Department Transcript Course Section Attributes Each entity mentioned above is supposed to have a number of characteristics associated with it. The characteristics relevant to the transcript generation system can be proposed as follows: 1. Student For the entity of student it is essential that the following characteristics be recorded: The name of the Student ‘The Registration Number of the student’. This number is assigned to it at the time of enrollment by the university itself. This is like an identification number for the student with which he is recognized in all the university documentation. It is a unique number Normalization To achieve the first normal form the primary key focus is changed in order to discriminate repeated groups. For example for the following un-normalized entity the focus is on the Dept# as a primary key. Department (Dept #, Dept Name, Dept Description, with repeated group of student i.e Student #, Student Name, Student Address, Student Phone, Student DOB, Student Sex, ……….) The first normal form can be achieved by just switching the focus of Student #. 1NF Student(Dept #, Dept Name, Dept Description, Student #, Student Name, Student Address, Student Phone, Student DOB, Student Sex, ……….) To achieve second normal form full functional dependencies are to be observed. Attributes that are not fully dependant are isolated as another entity with the respective primary key. For example for the above mentioned entity, Student (Dept #, Dept Name, Dept Description, Student #, Student Name, Student Address, Student Phone, Student DOB, Student Sex, ……….) The 2NF would be, Department (Dept #, Dept Name, Dept Description, Office Number, Office Phone, College) Student (Student #, Dept #, Student Name, Student Address, City, State, ZipCode, Student Phone, Student DOB, Student Sex, Class, SSN, …..) While there are significant non key attributes that are fully dependent on “Department” entity, therefore to achieve second normal form a new entity namely department is introduced with its respective primary key and dependent attributes. To achieve 3NF mutually dependent attributes are isolated as separate entities with independent primary key. The third normal form for the above mentioned scenario would be, Student (Student #, Dept #, Student Name, Student Address, CityID, State, ZipCode, Student Phone, Student DOB, Student Sex, Class, SSN, …..) City (City ID, City Name) The third normal form is achieved when the attributes cannot be decomposed further i.e. they become atomic. Entity-Attributes Diagram Figure 1.0 thru 1.4 illustrate the entity attributes diagrams for all possible entities. Discussion of Entities: The student entity has one composite attribute which can further be decomposed into the attributes namely City, State and Zip Code. Figure 1.0 Figure 1.1 Figure 1.2 Figure 1.3 Figure 1.4 Entity Relationship Diagrams The two entities namely Student and Course are linked to each other in a many – many relationship. Figure 2.0 highlights the respective relationship. A student can take multiple courses while a course can be taken by multiple students. However it is possible that a course is not opted by any student. On the other side a student must opt for at least a course to maintain the status of a student. Figure 2.0 The process of registration explains the link between these two entities to a greater extent. Figure 2.1 highlights that a student gets registered with a course and a course is offered to students for registration. Figure 2.1 Similarly the relationship between section and courses exists in the same manner. Multiple courses are taught in a section while a course may be taught in multiple sections. The respective elaborated ER diagram is presented in Figure 2.2 Figure 2.2 The relationship of students with their department and their section are of same type i.e. one – many. A department or a section may have multiple students but a student may get associated with a single section or department at a time. However for a section to exist at least one student is required to study into it while there is no such restriction for the existence of a department. The relationship between Student and Grade Report will always be one – one. In general there will always be one grade report for each student but this case is a bit tricky. If the grades are recorded course wise or semester wise the there may exist multiple records against the id of each student. This is done in order to allow gradual updates. The fact is that whenever a student’s marks report is queried there will be just one up to date consolidated report. Figure 2.3 highlights the E – R model. Figure 2.3 The entities “Instructor” and “Section” are linked in a many – many fashion. An instructor may remain engaged teaching at multiple sections while a section allows multiple instructors to teach. There is must be at least one instructor for a section’s existence. Same goes for the instructor. Figure 2.4 exhibits the respective E-R diagram. Figure 2.4 Assembling all of the individual E-R diagrams would result in figure 2.5 appended below. The presented E – R diagram indicates the data model of University database required to record student grades. The diagram clearly explains the phenomena that some of entities like Student and Instructor are not related directly. The entity Section relates a student with its teacher. This indirect relationship allows multi table queries to execute and to produce required results. For example the list of students studying with a particular instructor can be easily derived from the above mentioned relationship. The finalized ERA Diagram is presented as Figure 3.0. It holds all the relationships and attributes considered for the university database that is designed to record students’ grades. Figure 2.5: Entity Relationship Diagram of University Database for recording student grades. Figure 3.0: ERA Diagram of University Database for recording student grades. Read More