Assessment R001: Coursework

Weight: This Coursework counts for 100% of this module.

Subnission deadline: August 5, 2022 @ 1700 HRS

Maximum Marks: 100

The course work will be assessed for the following learning outcomes:

A. Demonstrate a basic understanding of the design of databases.

B. Show a fundamental grounding in the operation and usage of database management systems including "hands-on" experience of a basic database management system.

C. Demonstrate in-depth knowledge of the database language, SQL.

D. Show understanding of the legal processes and implications of creating and maintaining information systems.

Assessment Tasks:

Your task is to design and develop a database application for the given business scenario. You will work your way through the conceptual, logical, and physical designs of a DBMS solution. You will apply your knowledge of application development to develop and deploy a DBMS backend application. This will require substantial research of best practices in design and the legal and ethical standards to which you must adhere during design. The skills required in this assessment will be valuable in the role of a DBMS professional or an IT manager, as these individuals are often tasked with developing solutions to various organization data problems while also adhering to legal, ethical, and financial considerations.

There are the following 6 parts for this coursework:

1. Identify business requirements (10%)

2. Conceptual design using Entity-Relationship Diagram (ERD) (15%)

3. Normalization (15%)

4. Relational Model (15%)

5. Physical Design (Create tables, insert sample data into the created tables). (15%) 

6. Construct SQL queries that can be run against the database. (20%)

7. Construct Relational Algebra queries that can be run against the database. (20%)

You will be required to write a brief report for each component and note down your process, thoughts, and assumptions made. You will also need to construct an APEX database and produce a set of queries that can be run against that database.

Marking Criteria

This coursework will be marked out of 100. Please see the assessment rubric at the end of this document outlining the criteria for assessment.

Project Scenario

You are a small database consulting company specializing in developing databases for the medical industry. You have just been awarded the contract to develop a data model for a database application system for a mid-size health insurance company to keep track of health claims including patient information, provider(doctor) information, information about patient visits to their doctor as well as prescription drugs prescribed to patients.

Information such as patient name, address, phone, email etc,. are needed as well as who each patient’s primary care doctor is, their insurance ID number and insurance company name.

We also want information on each doctor such as their speciality and what hospitals they are affiliated with as well as their phone, address etc. Regarding the hospitals themselves, we will need to know where they are located and how to contact them.

The prescriptions given to each patient by a healthcare provider also need to be tracked in this particular database at this time to determine claim eligibility including some basic information on the drug being prescribed to make sure there are no conflicts with a patient’s other prescriptions. We need to know each drug’s name, purpose/use and possible side effects.

Eventually, the database will be used to track trends and for some extrapolative modeling based on the accumulated data. The database will be accessible in English only right now, although plans include making it available in multiple languages eventually.

Setup

You can complete this coursework either using Oracle Application Express (APEX) or SQLite. All you need to submit SQL scripts for constructing and querying the database along with the report.

Q1:  Identify Business Requirements (10%)

Using the text provided in the scenario above identify the business requirements that will allow you to understand the business processes involved in running this type of organization. Build a list of business needs, rules and assumptions based on the scenario as well as some research. Use the following categories to help you with this:

Business rule: It is used to understand business processes and the nature, role, and scope of the data. The first one is completed for you:

· A doctor may treat one or more patients and each patient must be treated by one doctor.

Assumption: It can be defined as a fact or a statement that has been taken for granted.

Problem: It can be defined as a situation or scenario that requires attention and a possible solution to alleviate the situation.

Q2:  Entity-Relationship Modeling (15%)

Using the above scenario identify any potential entities that will have to be represented in a relational database system. Entities are usually the nouns in the scenario description however not every noun becomes an entity so think carefully but remember you are identifying potential entities not creating a definitive list.

Identify potential attributes that will be used to store information about the previously identified entities. Attributes are normally found by identifying nouns that describe other nouns (our entities). Identify optional(o) and mandatory(*) attributes. Identify any potential candidate key (attributes that can be used to uniquely identify an entity)  and list it with ‘#’ sign and move it at the top of the attribute list. At this point, you should have a list of entities with attributes either optional or mandatory and candidate keys for each entity as shown in the below figure.

 

Identify relationships that represent an association between two or more entities. Using the business rules, identified in Q1, identify potential associations between entities, the relationship must capture the optionality of a relationship between both entities(remember the relationship exists in both directions). The first one has been completed for you.

 

· Each patient must be treated by one doctor

· Each doctor may have one or more patients

Your task is to create entities, their attributes and the relationships between the entities and construct the ERD that will show how the system is linked together.

Q3: Normalization (15%)

Take the un-normalized data that is currently represented in the ERD and apply the principles of the First, Second and Third Normal Forms so that the data can be said to be in 3NF. Note that some entities might be in some normal form already, if so, briefly explain them.

Tips:

· The first normal form states that all attributes have a single value - no multivalued attributes. For example, each patient can only have one primary doctor, each doctor can only have one speciality etc.

· The second normal form says that all attributes must be dependent on the entire key of the entity. For example, we need to know each drug’s name, purpose and side effects but if we include this in the Prescription entity it will be dependent only on what drug is prescribed not who it’s for or what doctor prescribed it – so it does not belong in the same entity as the prescription information itself.

· The third normal form states that no non-UID attribute can be dependent on another non-UID attribute. For example, A patient’s insurance ID number will determine what insurance company they are insured with. The ID number determines the insurance company’s name.

In the normalization process, you might create some new entities while resolving functional, transitive and multivalued dependencies. Write down the final list of entities in relational notation. The first one is completed for you.

Patient( patient_Id, Name, Address, phoneNo, email, doctorID, InsuranceCompanyID), where patient_id is primary key and doctorID, InsuranceCompanyID are foreign keys

Q4: Relational Model (15%)

In this exercise, you will take your normalized data model that was created in the previous step to map them into relation. Use table instance chart to map ERD into a relational model. The table diagram helps you map out a table before creating it in the database. You should describe the design of each table, by completing the table instance chart for each table mapped. Below an example is given for your reference.

Note: You can use Oracle SQL Data Modeler and engineer it to a Relational Model. In that case, you need to create your final ERD using Oracle SQL Data Modeler and use ‘Engineer to Relational’ option to create the relational model. The table instance chart will help you to verify that results matched with your table map.

Q5: Physical Model (15%)

Create a physical database design that builds on the conceptual and logical models you crafted.

5a) Write the SQL DDL statements to create the fully normalized database. The SQL should contain CREATE statements for each table. You should include indexes and foreign keys where appropriate, and list and justify these in your report. Be sure to include appropriate constraints.

5b) Write INSERT statements to populate the new tables. Insert enough records (at least 10 rows in each table) so that you can run interesting and nontrivial queries on your database.

Tip: You have to consider the order of the tables when populating them. A table that has a foreign key field cannot be populated before the related table with the primary key

The SQL statements to create the tables and insert records should be saved as DDL.sql and data.sql and submit your scripts along with your report.

Q6: Querying (20%)

In this exercise, you will retrieve data that is stored in the database system by using a SELECT statement to demonstrate your SQL skills.

You are required to write at least 10 queries demonstrating different SQL operators. Write retrieval statements in English, SQL code and provide screenshots of your results, also save your query code as SQLScript.sql and submit it along with the report.

Please do not list very basic operations that only show the tuples of relations. Challenging queries would amount to higher marks. Each query should showcase different SQL skills. Challenging queries would amount to higher marks. The complexity of a query will be measured by the number of joins, select conditions and GROUP BY clause, where a select condition counts as a ‘1’ and a join count as a ‘2’ and GROUP BY condition count as ‘3’ (so, a SQL query that probably involved two select conditions, one join condition, and GROUP BY clause will be counted as the complexity of ‘4’).

Q7: Relational Algebra Queries (10%)

Write down five relational algebra queries that can be evaluated with your schema definitions. You can either write equivalent relational algebra queries of Q6 or write different queries. In any case, you should state the English specification of the query, as well as state the query in relational algebra expression.

The complexity of a query will be measured by the number of joins, select conditions and aggregate function, where a select condition counts as a ‘1’ and a join count as a ‘2’ and an aggregate function count as ‘3’ (so, a relational algebra query that probably involved two select conditions, one join condition, and an aggregate function will be counted as the complexity of ‘4’).

Submission

Report

You must write your report in MS word and submit it as a PDF. The name of the pdf file should be

yourID_report.pdf.

Your report should contain:

The first page of this document with your ID for grading purpose

You must have a /section for each part of the coursework, and they must be named as follows:

1. Business Requirements

2. Conceptual Model

3. Normalization

4. Relational Model

5. Physical Design

6. SQL Queries

7. Relational Algebra Queries

Files

Your submission should be a single zip file. Your submission must include the following in this structure:

1. Report.pdf

2. ddl.sql: The DDL statements of the normalized database

3. data.sql: INSERT statements of the normalized dataset

4. sqlScrip.sql: SQL statements to run queries against normalized tables