hi , I have an assignment in sql language , I have uploaded the files describing the assignments.. the screenshots describe what needs to be done and the word file is a sample so you can follow for doing my assignment.. the assignment needs to be done on the phpmyadmin
z 1. Introduction 2 | P a g e Nowadays, whether we are aware of it or not, databases are essential in our lives. Almost everywhere , there is a database behind when we search something on Internet or purchasing/renting a bookonline or booking our holiday for the next summeror etcetera, the list can be endless. But even though the databases have so much use at all the time, there are just few of us that really understand the mysteryof how it comes to life. During this module, I’ve step out of the blind spot and entered in this world of knowledge,which I find very challenging. 2. Understanding of database Based on the assignment scenario, New University is asking me to develop for them a database which will put together all their requirements.The data that needs to be organizedin this case will be all information about their departments, courses, modules, students, lecturers and others employees of the University, but the application needs to be also able to run reports about students attendance for a specific time and grades for specific course and module. SoWhat is a database? A container ( for example a library as a physical location) used to store organized data ( books ), in a predefined way, in order to manipulate, restore or change them whenever required. For all this work with the data we need a special engine known as Database Management System or DBMS. This is a combination of computer software, hardware and information that handles and manages data systematically and without which it will beactually impossible to access the database. There are different manners in which data can be arrangedand after that manipulated.The logical structure of information that defines the relationship between different parts of data is defined as database model. The most studied database models are briefly explained below: The predecessor for the DBMS is File Management System. It is a system that allows access to single filein a particular moment.Using this approach, each user creates and implements the needed files only to run a specific program.(Fig.1) StudentsStudentapplication programStudent files CoursesCourse application programCourse files Fig.1 For example, in the New University scenario, one user will be maintaining the details of the students and another user will be maintaining the courses and modules details, or the employeesinformation. I need to mention that data sharing is very complexor almost inexistent in this situation. Because of this reason same data is wasting space being held multiple times by different programs or, even worse,users may be unaware of potentially useful data held by other programs. Also, when data is redundant, it is difficult to change something. Looking back to our case study, for example if I want to update one specific student address, then I have to make changes at all the places where data of that student is stored. If by mistake, I forgot to change the address at one place then data inconsistency will occur. In file system the data is used in various formats, have no standard,running on different programs,which will conduct to data isolation and will make very difficult to find a specific data item if I want so.There is no or very less security as well. Furthermore, the value of data in the database must satisfy some rules. For example the New University have a policy that the age of an employee must be >=18. The value which is not satisfying this constraint must not be stored. In file system, there is no procedure to check these constraints automatically. On the opposite side, theDatabase Management System (DBMS) allows access to multiple files or tables at a time.Using this kind of approach,a single repository of data is maintained( is defined onceas data + complete definition + description of structure and constraints and then accessed by many users). Due to centralized systemand use of standard format, data can be shared very easily and because there is no or less data redundancy, data remains consistent (for a specific studentwillshow the same details to every user of the application). Also, if I want to search for aidentified item, the system is very accessible and user-friendly, querying operations being already available. From the security point of view, DBMS have high level like passwords, encryption, fingerprint matching, face and voice detection, etcetera. Though, each user should be restricted to see only those data items to which he is entitled to have access, in order to avoid compromising this security. Although DBMS brings many benefits in the use of computerized data ( as accessibility,productivity, responsiveness) though there are also risks assumed once this choice is made.For example, if one of the New University application users will insert wrong data (like a wrong list of students for a specific course) into the database ,this will affect not only thatprogram, but any others which use that piece of information. Others major disadvantages of DBMS that needs to be mentioned here are the size and the complexity of this system, the expensive costs of the software itself plusthatit may generatecosts for the hardware as well and the loss of data in case of a system crush, which will surely impact all the business activity. Fig.2 The Hierarchical Modelhave a structure similar with a tree. Between two types of data allows a link with one-many relationship. Each unit has only one root , but one root can have several units. (Fig.2) Fig.3 The Network Model is also known as many-many relationship model. In this database many records are connected to one parent file. Can be viewed astree that is held upside down where several branches end up in one parent field. A Relational database management system (RDBMS) is a DBMS that is based on the relational model as introduced by E. F. Codd in 1970.The basic data structure of the relational model is the table, where information about a particular entity ( student) is represented in rows and columns. There are two main important approaches in the software development process . One is breaking the problem in small pieces , adding more and more details for each sub-level (top-down or decomposition) and the other builds the structure by coding and testing step by step without having the complex image of the program and how the parts are linked together (bottom-up).Re-usability of code is one of the main benefits of the bottom-up approach, but in this case I will use the first approach that I have mentioned. 3. Conceptual Modelling Based on the case scenario all the possible entities that I can find are: · Attendance · City · Courses · Date · Day · Departments · Employees · Grades · JobDescription · Level · Modules · Postcode · Qualification · Session · State (Country) · Students · Timetable Fig.4 4. Logical Modelling Using Normalization (Fig.5)design technique I split all the entities that I found in the case problem in smaller tables and link all together with the appropriate relationship. First of all , the requirements of the problem became my UNF (Unnormalized Form). To get to the 1NF column,I just followed the rules: describe each entity by it’s attributespaying attention that data should be atomic ( each cell only contains a single value) and make relationship using primary key(the unique identifier with no NULL value ever) and foreign key(connect the tables by referencing to the primary key of another table ). For all repeating groupsthat I found in my 1NF, I create separate table inthe 2NFand identified them withunique primary key, also relating them with the others tables by foreign key. Furthermore, to create my 3NF column I just make sure that all the information displayed in my 2NF has no transitive functional dependencies. 9 | P a g e Fig.5 10 | P a g e ERD with all the normalized entities containing the relevant attributesFig.6 Fig.6 ERD with all attributes and their data type Made in StarUML Fig.7 ERD with all attributes and their data type Generated by PhPMyAdmin Fig.8 5. SQL and database implementation Structured query language or SQL is a specifically designed language for interacting with a database.The SQL command used to interact with relational databases are categorized into three major groups namely; DDL(Data Definition Language) that include CREATE, ALTER and DROP; DML(Data Manipulation language) that include SELECT, INSERT, UPDATE and DELETE and DCL( Data control language) that has GRANTand REVOKE. In the following slides I will show all my work done to implement the New University relational database. Fig.9 Fig.10 Fig.11 Fig.12 Fig.13 Fig.14 Fig.15 Fig.16 Fig.17 Fig.18 New University Database Fig.19 Courses Fig.20 Modules Fig.21 Employees Fig.22 Students Fig.23 Job Description Fig.24 Update data Fig.25 QuestionGrades Fig.26 Question Attendance Fig.27 Delete data from tables Fig.28 Alter data Fig.29 Drop = Delete structure Fig.30 6. References · Lecture slides presented on Moodle Page. Available at: https://partnerships.moodle.roehampton.ac.uk(Accessed: 01/07/2018) · Forta, Ben (2012) SQL Crash Course (12th Edition)SAMS. ISBN: 0672327120 (Accessed: 08/04/2018). · Howe, David(2001) Data Analysis for Database Design (3rd Edition) Butterworth-Heinemann. Available at:https://www.vlebooks.com/vleweb/Product/Index/33899 (Accessed: 01/07/2018) · Ullman, Jeffrey D (2013) A first course in database systems , Pearson, Available at https://www.vlebooks.com/vleweb/Product/Index/437632 (Accessed: 01/07/2018) · https://www.tutorialspoint.com/sql/sql-rdbms-concepts.htm (Accessed: 02/07/2018) · https://en.wikipedia.org (Accessed:01/07/2018) 7. Appendix newuniversitysql (1).sql