Design a data model for a given scenario

Assignment Help Database Management System
Reference no: EM131303089

Identify and implement database models.

Examine and design a data model for a given scenario.

Describe and apply SQL to create tables and generate queries.

Design, develop and implement advanced database solutions using SQL statements.

Design, develop and implement advanced database solutions using procedural languages.

Demonstrate good time management as a team, in the application of problem solving techniques in relation to database management.

Database Systems

Using only ORACLE, design and implement a realistic database system and develop a range of SQL queries based on the case study below. For the deliverables c, d & e enclose the necessary screenshots without fail.

You are required to submit:

a) A fully UML annotated E-R diagram 1 and 2 showing the entities, primary and foreign keys, composite keys, relationships and optionality.

b) A relational schema of your database in 3NF, clearly indicating attributes, the data type of each attribute, primary and foreign keys and which attributes are nullable, giving reasons. List any assumptions you need to make.

c) Listings of the records in each table. There should be a minimum of ten records in each table.

d) Ensure consistency of data in the database by developing integrity and referential integrity constraints that will ensure the correctness of the data when it is entered.

e) Produce SQL listings on the specific questions based on the Case Study.

1. List all students' details and order by student Id.

2. List of student reps in all programmes

3. List the names of all students who are enrolled on the BSc Computer Science and are doing the Database module.

4. Find the student with the minimum marks in a specific module, giving the name of the student and module.

5. Give a count of the number of students on a specific module that started on 1st September, 2015.

6. Give total credits acquired by each student.

7. List the academic's name and salary and their Principal manager's name and salary for all academic staff who earn more than their managers.

f) Documentation must be logically organised with clarity and completeness.

TEAM WORK: Documentation stating how each group member participated in the development and completion of the assignment.

g) Group presentation - presenting the design and implementation of the solution in Oracle.

Case Study:

Wisdom University is a prestigious institution educating students from all over the world. It offers 40 programmes from five disciplines. The university currently uses an old legacy system which is file-based. Due to various difficulties, the management has decided to modernize the system by having an online system with a backend database.

As a group of consultants, you are required to produce a database system to upgrade a part of the system that handles the student registration and timetabling of students. On arriving at the university each student must be enrolled on a programme and be allocated a personal tutor who would oversee their academic needs at the college. The maximum duration of a programme is 6 years for part-time, and 4 years for full-time mode. The minimum and maximum number of modules that make up a year of a programme is six to eight modules for full-time, and four to six for part-time students. Each module weight 15 credits, students are required to obtain a total of 360 credits to be eligible for an award.

Each programme must have a student representative. The student rep is the first point of call for fellow students with issues related to the programme, modules, and teachings.

Each student must take and pass each module in a given year before they are allowed to move into the next year of the programme or to graduate. A student is normally allowed two attempts to pass a module. A particular module can be offered as part of one or more programmes.

The university has 5 departments, each of which offers a number of programmes. Each department has a Head of Department (Dean) and each programme has a Programme Leader. Each module is assigned to a Module Leader who oversees the teaching and assessment of the module. Principal Lecturers manages other teaching staff, which can also include Programme Leaders. Staff details, such as, contact telephone numbers, speciality, qualification and awarding institutions and previous employment must be recorded.

In order to achieve the business needs and ensure consistency of data in the database you are to develop integrity and referential integrity constraints that will ensure the correctness of the data when it is entered. Below is a list of checks you should implement.

1. For this study, student gender should be recorded as ‘M' for male or ‘F' for female.

2. The name of a student/staff should not contain any numeric characters.

3. The staff salary should be greater than £10,000 and not exceed £150,000.

4. Student email address should have an @ symbol present to be valid.

5. The classification of module results must only be one of ‘F', ‘P', ‘M', ‘D', ‘NS', ‘FX'.

6. Each student's start-date of a programme must always have a value.

7. Each student's end-date of a programme should not be earlier than their start date and it should be minimum of 3 years.

8. The student degree graduation date should be greater than ‘27-Oct-2015'.

9. Staff and Student numbers should be generated automatically using a sequence and a trigger.

Reference no: EM131303089

Questions Cloud

Does gold have intrinsic value : Does gold have intrinsic value? Why might Buiter have said that it didn't?- In what sense does a bubble in the price of an asset result from a "set of self-confirming beliefs"?
Determine the appropriate process control charts : Determine the appropriate process control charts that will be used to monitor the performance of that process and determine whether it is predictable (in control). Describe the data you plan to use and operationally define how it is to be collecte..
Create market segment profiles for the purpose of targeting : In marketing, segmentation is used to create market segment profiles for the purpose of targeting consumers. Marketers identify a target market as one of the following three different strategies:Undifferentiated targeting,Concentrated targeting.
What was the trend from 1959 to 2008 : What was the trend from 1959 to 2008?- What happened to the monetary base total numbers from 2008 to 2010?
Design a data model for a given scenario : CN5101 -design and implement a realistic database system and develop a range of SQL queries based on the case study below. For the deliverables c, d & e enclose the necessary screenshots without fail.
What is the aim of monetary policy : What is the aim of monetary policy? What is meant by economic well-being?- Briefly define Price stability monetary policy goals.
Describe vision for career : In 750-1,000-words describe your vision for your career once you have completed your master's degree. In your discussion, address the impact completing this degree will have on meeting the greater social good in your industry and within the commun..
Expalin major complexities that would arise under expansion : Examine the major complexities that would arise under expansion via capital projects. Propose key actions that the company could take in order to prevent or address these complexities.
Overview of your target position and industry : UNIV B201 Career Management Development Plan Instructions. You will write a two paragraph overview of your target position and industry. This information will include employment outlooks and projections, required education and licensure, and averag..

Reviews

Write a Review

 

Database Management System Questions & Answers

  Give a list of employee names

Using the Northwind database - Give a list of employee names and each employee's corresponding customers by company name.

  Select the primary key from the candidate keys

How do you select the primary key from the candidate keys? How do foreign keys relate to candidate keys? Provide examples from either your workplace or class assignments.

  Design an relational model model of a database

Design an Relational Model (RM) model of a database (DB) that will be useful for each of the chosen enterprises. (Hint: You may choose to translate the 2 ER model designs you created ).

  Design an entity relationship diagram

Design an Entity-Relationship diagram for the auction database and enter the design using a data-modeling tool such as ERWin.

  Identify three characteristics associated with big data

What is the difference between projecting and joining? -  What is big data? Identify three characteristics associated with big data.

  Explain the level of normalization

Use Microsoft Access to create the keys and relationships between tables for the Huffman Trucking Driver Log.

  Create all necessary and appropriate relationships.

Create all necessary and appropriate relationships.

  Discuss the garbage collection in a structured programming

Discussion Compare and contrast garbage collection in a structured programming languages

  Explore ways of converting depositors to borrowers

Universal Bank is a young bank growing rapidly in overall customer acquisition. The majority of these customers are depositors with varying sizes of relationship with the bank. The customer base of borrowers is quite small, and the bank is interes..

  Create an xy plot of ecg vs. time

Create an XY plot of ECG vs. time, and place the plot on the Plots worksheet. Give the plot an appropriate title and labels for axes, and adjust various parameters of the plot to make it look pleasing and professional. Resize it to approximately..

  Torri manufacturing corporation -determine unit product cost

Determine the unit product cost of each of the company's two products under the traditional costing system

  Construct an idef1x entity-relationships diagram

Name each relationship and ensure that you have correctly defined the minimum and maximum cardinalities.

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd