Assignment: Designing and implementing Student DBMS for WLCBMS College

The purpose of this assignment is to understand, design, query, implement, test and maintain a database(s). Learners will also have a theoretical insight into the requirement for designing a database that meets a given user or system requirement and that is functional, user friendly and robust.

Aim

To enable learners to apply the business skills needed to design an e-Commerce solution for an Organisation.

Learning outcomes

In this assignment learners should demonstrate achievement of:

1. LO1 Understand data models and database technologies

2. LO2 Be able to design and implement relational database systems

3. LO3 Be able to use manipulation and querying tools

4. LO4 Be able to test and document relational database system

SCENARIO

Assume that you are required to build a Relational Database System for WLCBMS College.

Some information about the college:

The college has many departments such as the Business, Computing, Training(More on http://www.wlcb.co.uk).

The college keeps data about students, marks, staff, courses, classrooms, etc. Each department offers degrees (programmes) for undergraduates and postgraduate programmes. For example The department of Computing offers degrees in Computing, Business IT, Mathematics, Digital Media, Networking etc.

Different degrees have different fees - according to department, type of degree (Postgraduate, undergraduate, HNDetc) and type of students (oversee, etc). The fees can be paid in full (a discount would apply) or using a payment plan (in instalments). Also, fees can be taken just for a particular course (in case the student fails an assessment)

Students enrol on a programme, and within it they study courses. For each course, marks are given for assessments (exams and/or coursework/assignment). The marks are recorded on the student marking system. For the simplicity of the system all courses will be a year's long, start at the same time and have 2 semesters.

Each department has also a register system, where the student attendance for each class is recorded.

The management team wants information at university level about students' performance, such as:
• Statistics about the origin of students,
• Statistics about payments, late payments, etc.
• Statistics about marks for each department and students' performance in exams/coursework.

The performance office team would like to find out different statistics about students' attendance, such as :

• What time of the day has better attendance?
• Which courses are more popular?
• What is the average attendance for each particular lecturer?

TASK-1 -Requirement Specification

a) Discover and document the requirements of the system. Apply the fact finding methods (e.g. document checking, observation, interview, survey, questionnaires, research, group work etc) for requirement elicitation. Give some examples of the questions you prepared for the interviews and questionnaires/surveys and some documents you checked for requirement analysis. Assemble the corresponding Business Rules.

b) Critically discuss the advantage & disadvantages of different fact finding methods applied for requirement specification and your experiences, assumptions and choices.

Note: For this purpose you can check the documents (attendance register, college prospectus, fee payment receipt etc). You can arrange interviews with the management team of the college or ask them to complete survey/questionnaires.

TASK- 2 -Design Relational Database Systems

Based on the given scenario and the customer requirements, develop the process and data model for WLCBMS College DBMS. You can make any necessary assumptions and improve the design accordingly. More information can be taken from the college website, as well. Your report should have a section on the analysis and design, including a discussion about your assumptions and choices.

a) Describe & critically compare different data models and schemas.

b) Draw a Context Diagram and a high Level Data Flow Diagram to depict the given system.

c) Draw Entity Relationship Diagram (ERD) identifying the main Entities and Relationships indicating the degree of relationship and optionality.

• Explain entity types and Key attributes and their types in a data model.
• List the Primary key and foreign key(s) for each entity (list the name of the table it is in and the name of the table it references) stating the assumptions you made.
• For each relationship in your model, explain the choice of optionality in each direction. State any assumptions made.

d) Discuss the purpose of Normalisation. Describe different normal forms and issues around the degree of normalisation chosen for your design.

e) Sketch out your design as a series of screens or storyboard.

f) Critically discuss about the functionality, reliability, consistency and performance of your designed system.

TASK-3 -Implement the Relational Database System

a) Build a relational database system based on a prepared design using any using appropriate RDBMS software, e.g. MS Access, SQL Server, Oracle etc. Make sure you follow all the necessary steps in building the database. In building the database, you are expected to include the following:

• Tables, fields, relationships
• Data types
• Referential constraints
• Views, indexes etc. (optional)

b) Critically analyse the process of converting the logical database design to a physical implementation.

TASK-4 -Querying/Reporting database system

a) Implement a query language into the relational database system. Obtain from the Users some representative enquiries for the Database. Formulate the SQL queries to produce some reports. Make sure that you have enough data in your database to show reports.
For example,
• List all the part-time students enrolled in the Postgraduate course.
• Calculate the average mark of the undergraduate students for the particular module etc.
b) Explain the benefits of using manipulation and query tools in a relational database system.
c) Critically evaluate how meaningful data has been extracted through the use of query tools

TASK-5 -Testing & Documentation

a) Create a Test Plan that includes:
• What elements of the system are going to be tested(e.g. Tables, Relationships, Queries etc)
• What types of testing will be carried out (e.g. Component testing, Integrative testing, Systems testing, Acceptance testing)
• What test data values will be used to test the system (e.g. Normal values, Extreme values, Exceptional values)

b) Now critically review and test your relational database system.

c) Create documentation to support the implementation and testing of a relational database system.

d) Explain how verification and validation (e.g. format check, check digits numerical data, check data type etc) has been addressed.

e) e) Explain how control mechanisms have been used.

f) Create user documentation for your developed relational database system.

TASK-6 -Discuss new database development technologies

Discuss the following with example:
• Dynamic storage
• Data mining and data warehousing
• Web enabled database applications
• Multimedia databases
• Document management systems
• Digital libraries