Blog

Data Modelling & SQL Language

Learning outcomes assessed within this piece of work as agreed at the programme level meeting

On successful completion of this module students will be able to

1. Understand fundamental concepts of database management systems such data independence, data models and database technologies

1. Be able to design and implement relational database systems
2. Be able to use manipulation and querying tools, including the use of Structured Query Language (SQL) for managing data held in a relational database management system (RDBMS)
3. Be able to test and document relational database systems

Scenario:

New University:

New University is an international University who is looking to develop an effective RDBMS to cater their needs for their growing business. As a junior developer, you have been asked to develop a database for New University which will satisfy all their requirements.

The database should contain the information about students, Lecturers, departments, attendance, grades and employees of the University.

A student is enrolled in only one course but a coursecan contain many students.

For students, name, address, phone number, course related information to store. For lecturers, the University wants to record the name, address,identification number and time table information. Not all employees can teach but just the lecturers. For all these employees, it is required to record the qualification i.e. what level of class they can teach. For courses, it is required to record the course and modules details. The course has many modules. For attendance, the University needs to keep the record of attendance of each student. Relevant assumptions, if required can be made. Data Modelling & SQL Language

 Database should be able to get the following information from the developed database:

1. Statistics of the students, lecturers, course and modules
2. Number of presents /absent for a specific monthof the student
3. Average marks and grades of students for specific course and module

Assessment

60%: Assignment 1 (involving a case study)

Note: To pass this module:

• You must attempt all pieces of assessment and you must achieve at least 40% overall.
• If you have a re-sit, you must attempt all the elements you have failed as failing marks cannot be carried forward without attempting re-sits.

Word count: 1250 Max.

For written assignments, students need to state the word count at the end of the assignment. 10% over the stated word count is permitted without penalty. If students go beyond this, then there is a penalty of 5 marks for every additional 10% beyond the word count. The word count includes the Abstract or Executive Summary and all in-text citations. The word count excludes the Bibliography and Appendices. Please note that Appendices should only include supplementary information not provided in the main text. There is no specific penalty for submitting a piece which is below the word count, but please note that shorter written submissions are likely to attract poorer grades, particularly where they lack the necessary depth of analysis.

Tasks / Deliverables of the Assignment 1:

Marks are awarded for producing a properly documented system that meets the requirements as specified below:

Task 1:Understanding of database[ 20%]

By relating your discussion to the above scenario where applicable,

1. Discuss aboutRDBMS and file based systems. Your discussion should include the advantages, disadvantages and limitations of each.
2. Discuss the advantages and disadvantages of Hierarchical, network and relational database models
3. Discuss the strengths and weaknesses of top-down and bottom-up approaches to design/develop the database and justify the approach you will use for this task

[place-order]

Task 2:Conceptual Modelling[20%]

1. Identity all the possible the entities that you can find in the problem scenario and list them in alphabetical order.
2. Using Crow’s foot notation, design a conceptual data model for the New Airline database in a modelling tool of your choice. All entity types and the relationships between them must be clearly shown. Your design should show all cardinality and participation constraints accurately. Data Modelling & SQL Language

Task 3:Logical Modelling[25%]

1. Discuss the principles of normalization and demonstrate by examples the steps you followed to achieve normal forms in relation to the given scenario. Your demonstration must show the full process with explanations, starting from UNF, 1NF, 2NF to 3NF. (There is no requirement here to go through the full list of attributes in the data model for the New University).
2. Produce an ERD with all the normalized entities containing the relevant attributes. State any de-normalization, if any, in all your relations and clearly state the reasons for de-normalizing relations in your implemented solution.
3. Map the ER model devised above into a set of logical relations in the relational data model.

Task 4:[30%]

1. Produce a script using appropriate SQL commands (DDL) to create a set of database tables using any relational database. Your tables should also show all constraints applied at either the column or the table level. You are expected to implement the database so that it meets the requirements described in the scenario.
2. By using appropriate SQL statements, populate all the tables in the database you created with some data. The data should be meaningful but does not need to be extensive.

[place-order-2]

Referencing and Bibliography:   [ 5%]

Although much of your report will contain existing body of knowledge, you must write your assignment with your own words to demonstrate your understanding of the subject. You are required to follow the Harvard referencing system when citing others’ work. An accompanying list of references must also be provided as part of your report. Extensively referenced work reflects the level of research you conducted in the process of producing the document. It is also an acknowledgement of other people’s work. Correct referencing demonstrates your academic and professional skill. It also reflects your academic honesty and thus to some degree protects you from cases of plagiarism.

 Preparation Guidelines for Assignment Report

1. All components of the assignment report must be word processed (hand written text or hand drawn diagrams are not acceptable), font size must be within the range of 12 point to 14 point including the headings, body text and any texts within diagrams.
2. Standard and commonly used fonts such as Times New Roman, Arial or Calibri should be used.
3. Your document must be aligned left or justified with line spacing of 1.5.
4. All figures, graphs and tables must be numbered and labelled.
5. Material from external sources must be properly refereed and cited within the text using the Harvard referencing system.
6. All components of the assignment (text, diagrams. Code etc.) must be submitted in one pdf file.

Extensions to Assessment Deadlines and Late Submission

You may apply on grounds of mitigating circumstances for an extension to the deadline for this assessment. Applications for extension must be made sufficiently in advance. Extension may be granted where among other conditions the mitigating circumstances and supporting evidence are judged to be sufficient. Data Modelling & SQL Language

• Late submission, where the student submits work up to 2pm, seven calendar days after the deadline, will be accepted and marked. The percentage mark for the component of assessment will be capped at 40%.

Plagiarism and Collusion

 Any act of plagiarism (presenting another person’s published or unpublished work e.g. from a book or the web in any quantity without adequately identifying it and citing its source) or collusion (copying another learner’s assignment or work submitted by others in previous years and submitting it as your own effort) is a serious academic misconduct and will be seriously dealt with according to the regulations