SQL Programming Language for Relational Database Management System

SQL Programming Language for Relational Database Management System

Topic Objectives:

• Introduce students to SQL database management language.
• Provide an activity so students can experience creating a SQL program.
• Apply basic understanding of SQL to be used in the workplace.

Instructions

Scenario: You have been chosen as the new head of the information technology department at Goddard Hospital. The Centralized Microsoft Access database that was being hosted on a mainframe that the hospital has been using has failed been destroyed due to disk failure. The hospital also found it hard to scale up their database yet the data load is considerably increasing. And as such, the hospital thought it would be recommended to switch to a SQL server. Taking into account that many of the employee from various departments don’t understand how to add and retrieve data in the new database. You should simplify the explanation to suit the language needs of everyone involved. Moreover, you should also include the select statements to perform their daily tasks, including selecting data, adding, and deleting it from the database.

This assignment consists of three parts 10 theoretical questions, and 10 technical questions (SQL queries) 5 multiple answers questions. Please attempt all questions.

Below are examples of the type of queries expected from the student.

 Create a set of SQL statements that will create the following   table

CREATE TABLE Patient_Details (
Patient_ Name varchar(255),

Date _of _Service date (255)

Drug_Name varchar(255),
Drug_Prescription varchar(255),
Provider_Name varchar(255)
);

INSERT INTO Patient_Details (Patient_ Name, Drug_Name, Drug_Prescription, Provider_Name)
VALUES (‘ Linda Anie ‘, ‘6/10/2010’, ‘ Amiodarone ‘, ’25 mg ‘, ‘ Carl Smith ‘);

INSERT INTO Patient_Details (Patient_ Name, Drug_Name, Drug_Prescription, Provider_Name)
VALUES (‘ Idu George ‘, ‘3/24/2019’, ‘ Metoprolol ‘, ‘12.5 mg ‘, ‘ Park Won ‘);

INSERT INTO Patient_Details (Patient_ Name, Drug_Name, Drug_Prescription, Provider_Name)
VALUES (‘ Mini John ‘, ‘6/10/2020’, ‘ Atorvastatin ‘, ’40 mg ‘, ‘ Parikh Jane ‘);

INSERT INTO Patient_Details (Patient_ Name, Drug_Name, Drug_Prescription, Provider_Name)
VALUES (‘  Yolanda Brown’, ‘4/2/2020’, ‘ Amiodarone ‘, ‘5mg ‘, ‘ Pool Gray ‘);

Topic Activity

1. 1. Choose which SQL statement is correct to List all the records in the Patients chart:
2. SELECT DISTINCT class

from patients

1. SELECT *

FROM Patients;

1. SELECT name, surname, class

FROM Patients

Activity: Create a set of SQL statements that produce the follow output: using the table below answer the multiple choice questions.

CREATE TABLE Customer_Details (
Customer_Name varchar(255),
Address varchar (255),
City varchar(255),
PostalCode varchar(255),
Country varchar(255)
);

INSERT INTO Patient Details (Customer_Name, Address, City, PostalCode, Country )
VALUES (‘  Betahnia Abay ‘, ‘13100 pandora dr ‘, ‘ Berlin ‘, ‘12809 ‘, ‘ Germany ‘);

INSERT INTO Patient_Details (Customer_Name, Address, City, PostalCode, Country )
VALUES (‘  Ezra Mekonnen ‘, ‘11655 audelia dr ‘, ‘ México D.F. ‘, ‘07034 ‘, ‘ Mexico ‘);

INSERT INTO Patient_Details (Customer_Name, Address, City, PostalCode, Country )
VALUES (‘  Luke Ayele ‘, ‘2213 bronx rd ‘, ‘ México D.F. ‘, ‘06012 ‘, ‘ Mexico ‘);

INSERT INTO Patient_Details (Customer_Name, Address, City, PostalCode, Country )
VALUES (‘  Eldana Mekonnen ‘, ‘142 nox way ‘, ‘ London ‘, ‘ WC1 1AP ‘, ‘ UK ‘);

Multiple choice questions:

2. 2. Which statement below are correct for populating select all records where the city column has the value “Berlin”.
3. SELECT * FROM Customers

WHERE City=’Berlin’;

1. SELECT * FROM Customers

WHERE CustomerID=1;

1. SELECT Count (*) AS DistinctCountries

FROM (SELECT DISTINCT Country FROM Customers);

3. The following SQL statement selects the “CustomerName” and “City” columns from the “Customers” table:
4. SELECT *

FROM Customers;

1. SELECT CustomerName, City

FROM Customers;

1. SELECT *

FROM table_name;

,

Section 1: Theory SQL Questions

1. There are several types of SQL commands. Please state and briefly explain any 4 types with an example.
2. Constraints in SQL are used to set the rules enforced on the data in the table. These rules are used to enforced integrity and accuracy of the data. Give 5 examples of such constraints.
3. What is the difference between primary key and foreign key?
4. What are SQL Joins? Give two examples of joins in SQL.
5. Define normalization and list the three types of normalization.
6. When is it appropriate to use a view (give one instance)?
7. What is a stored procedure?
8. Normalization plays a big role in organizing structured data in the database efficiently. However, in some instances denormalization of the tables is necessary. What is the importance of denormalization?
9. Define a cross join with an example
10. Define the term subquery as used in SQL and state the two examples of subqueries.

Section 2: Technical questions

1. When a patient is admitted in the hospital, the following information about the patient is recorded: first name, last name, gender, date of birth, date of admission, ward number and the doctor attending to the patient. Create table patient that will be used to store this information.
2. The hospital also keeps records about the doctors: Doctor ID, first name, last name, gender, area of specialization, appointments. Create table doctor to be used to store the information about the doctor.
3. Insert into the patient table details of 20 patients.
4. From the details inserted into table patient, use a SELECT statement to display the information of the male patients.
5. Insert into table doctor details of 10 doctors.
6. From the details inserted into the table, COUNT the number of doctors’ areas of specialization.
7. Create a view Patient Doctor to display the patients last name, ward no and the doctor attending to them from the patient table.
8. SELECT from the patient table the details of the patients from the patient table who don’t have a doctor attending to them
9. Select from the doctors table, the doctors who don’t have any appointments
10. Write an SQL query to determine the number of patients admitted in the hospital.

Section 3: Multiple Answer Question.

1. Three of the following statements are true about PRIMARY KEY constraint in SQL. Which statement is wrong?

• The PRIMARY KEY is a unique identifier of a record in a table.

1. Primary keys must contain UNIQUE values.

• The primary key field can contain null values

1. Primary keys are also known as unique keys.

1. What is the function of ALTER TABLE statement in SQL?

1. Add columns
2. Add constraints
3. Delete constrains
4. All of the above

1. Which of the following SQL query is used to delete all rows in a table without deleting the table itself?

1. DELETE FROM table_name WHERE condition;
2. DELETE TABLE table_name;
3. DROP TABLE table_name;
4. CREATE TABLE table_name;

1. Which operator is used to determine if a particular record exists in SQL subqueries?

1. SQL WHERE
2. SQL NOT NULL
3. SQL EXISTS
4. NONE OF THE ABOVE

1. Which of the following is also known as a virtual table?

1. STORED PROCEDURE
2. OUTER JOIN
3. VIEW
4. UNION

Learning Outcomes

1. Understand, describe, define the components of the database and how to use these components to model a database;
2. Have an understanding of Structured Query Language (SQL) as used for database definition and manipulation and apply them in the assignment;
3. Design databases using the various modeling techniques:

a one-to-one (1:1) relationship

a one-to-many relationship (1:M)

a many-to-many (M:M) relationship

recursive relationships;

1. Create and define properly normalized database tables;
2. Implement the database designs to an SQL database system for the hospital.
3. Observe the core principles and pillars of information security that is: integrity, confidentiality, authentication, availability and confidentiality in implementing the database
4. Use the standard techniques and practices to database design and implementation.

Participation Guide:

1. Participation in the assignment is compulsory: Not participating equates to no marks awarded.
2. Answering at least two questions asked by either the instructor or a peer.
3. Asking questions and having constructive arguments during the assignment period.
4. Discussing the case, offering probable solute optimum solution and helping to pick out on the most optimal solution for the assignment.
5. Quizzes There will be two closed-book quizzes during the submission to ascertain the originality and clear understanding of the problem being solved in the assignment.
6. The design and implementation is that of a real-world database and you are expected to use the techniques and methods learnt in class.
7. This assignment should be mailed to the instructor upon completion before the specified deadline.

Assessment of assignment: 40 points

Section 1 – 10points

Section 2 – 10 points

Section 3- 5 points

Authenticity and originality: 5 points

Clear Understanding of the problem statement – 3points

Implementation- 5 points

Discussions: 2 points

Instructor Guide

Section 1: Theory SQL Questions

In this section look for certain keywords in the explanations for accuracy. The answers may be varied from one student to another. Also give a mark for the logic of the answers.

Question 1

1. • Data Definition Language– These are SQL commands used to define the database schema and permanently save all the changes in a database (Beaulieu, A. 2020). Examples include: CREATE, DROP, ALTER, TRUNCATE
   • Data Manipulation Language– This are commands used to modify tables (Beaulieu, A. 2020). examples: insert, update, delete
   • Data Querying Language-These are commands used to fetch data from a database (Beaulieu, A. 2020). Examples: SELECT
   • Data Control Language– These commands are used to manage access rights of various users of a database (Beaulieu, A. 2020). Example: GRANT, REVOKE
   • Transaction control Language– These is the commands used to manage transactions in the database (Beaulieu, A. 2020). Examples: COMMIT, ROLLBACK, SAVEPOINT

Question 2

• Foreign Key-This constraint is used as a unique identifier of rows /records in a different table (Tale, S. 2016).
• NOT NULL –This constraint is used to ensure that a column does not contain null values (Tale, S. 2016).
• UNIQUE– This constraint ensures that all the values in a given column are different/unique (Tale, S. 2016).
• INDEX– This constraint is used to create and retrieve data from a table easily and faster (Tale, S. 2016).
• CHECK– This constraint is used to ensure all the values in a column satisfy a given condition (Tale, S. 2016).
• DEFAULT– This constraint is used to set default values for columns or row s with a null value (Tale, S. 2016).
• PRIMARY KEY– This constraint is used as the unique identifier of a row and to achieve this it combines both NOT NULL and UNIQUE constraints (Tale, S. 2016).

Question 3

There are two possible answers

• A primary key is the unique identifier of a record in a table while foreign key references fields that are primary key in another table
• A table can have only one primary key whereas there can be more than one foreign keys in a table.

Question 4

Joins refers clauses or statements used to combine rows from different tables (two or more) using related columns between them.

Examples are: INNER JOINS, OUTER JOIN, FULL JOINS, LEFT JOINS, RIGHT JOINS.

Question 5

Normalization is the process of organizing structured data into related tables to eliminate redundancy, increase integrity, prevent anomalies and improve performance of the queries.

• Types of normalization include:
• First Normal Form (1NF)
• Second Normal Form (2NF)
• Third Normal Form (3NF)
• Boyce &Codd Normal Form (BCNF)

Question 6

• When you want to simplify or customize a complex table structure
• When simplification of the security model is necessary to by to filter out sensitive data and assign permissions in a simpler manner to different users
• When you want to change the logic and behavior of a table without altering the structure of the output structure.
• When you want to increase performance of a database since it optimizes query execution.

Question 7:

A stored procedure refers to a subroutine used to group one or more Transact-SQL statements into logical units and stored in the database

Question 8:

• Denormalization minimizes the SQL joins hence improving performance of a database.
• Maintain history that might have been lost during normalization.
• Simplify querying string, query response and in turn speed up the reporting time.

Question 9:

Cross joins also known as Cartesian join is used to combine each row of the first table with corresponding row in the second table. Example

SELECT      doctor_name
FROM        Patient
CROSS JOIN  Doctor

Question 10:

A subquery can be defined as a query nested inside another query.

Example:

SELECT first_name, last_name

FROM customer a, payment b

WHERE a.payid = b.payid AND b.Pay >

(SELECT Pay

FROM Payment

WHERE payid =  ‘P0076’);

Section 2: Technical questions

The codes may be varied and give a mark for SQL statement with the needed outputs. Also award points for clean and concise codes.

Question 1:

CREATE TABLE Patient (
First_ Name varchar(255),Last_ Name varchar(255), Gender varchar(255), D.O.B(255),DOA date(255), Ward_No varchar(255),Doctor_Attending varchar(255)
);

Question 2:

CREATE TABLE Doctor (
Doctor_ID varchar(255),First_ Name varchar(255),Last_ Name varchar(255), Gender varchar(255), Area_of_Specialization varchar(255), appointments varchar(255)
);

Question 3:

INSERT INTO Patient (First_ Name, Last_ Name, Gender, D.O.B, D.O.A, Ward_No, Doctor_Attending)
VALUES (‘ Ann ‘, ‘ Marrie’, ‘ Female ‘,  ‘6/10/1964’, ‘6/10/2014’, ‘ B213 ‘, ’25 mg ‘, ‘  Smith ‘);

This process should be repeated 20 times by the student and the data inserted might vary therefore mark the syntax of the code rather that the output.

Question 4:

SELECT * FROM Patient WHERE Gender= ”Male”

Question 5:

INSERT INTO Doctor (Doctor_ID, First_ Name ,Last_ Name,Gender, Gender, Area_of_Specialization, appointments)
VALUES (‘BEC087PED ‘,’Will ‘, ‘ Smith’, ‘ Male ‘,  ‘Pedetrician’, ‘1, afternoon’);

This process should be repeated 10 times by the student and the data inserted might vary therefore mark the syntax of the code rather that the output.

Question 6:

SELECT Area_of_Specialization COUNT (*)  FROM Doctor GROUP BY Area_of_Specialization;

Question 7:

CREATE VIEW Doctor_Patient AS

SELECT Last_Name, Ward_No, Doctor_Attending

FROM Patient;

Question 8:

SELECT *
FROM Patient
WHERE Doctor_Attending IS NULL;

Question 9:

SELECT *
FROM Doctor
WHERE appointments IS NULL;

Question 10:

SELECT COUNT(*) AS Number_of_Patient

FROM Patient

WHERE salary Last_Name IS NOT NULL;

Section 3: Multiple Answer Question.

Only give points for the correct answer chosen by the student.

1. (C. The primary key field can contain null values)
2. (D. All of the above)
3. (A. DELETE FROM table_name WHERE condition;)
4. (C. SQL EXISTS)
5. (C. VIEW)

REFERENCES

Beaulieu, A. (2020). Learning SQL: Generate, manipulate, and retrieve data.

SQL server stored procedures tutorial. (2020, April 11). SQL Server Tutorial. https://www.sqlservertutorial.net/sql-server-stored-procedures/

Reading Material

Denormalization: When, why, and how. (2019, February 13). Vertabelo Data Modeler. https://www.vertabelo.com/blog/denormalization-when-why-and-how/#:~:text=What%20Is%20Denormalization%3.F,create%20instances%20of%20existing%20tables

Faroult, S., & Robson, P. (2006). The art of SQL. O’Reilly Media.

Normalization in dbms: 1nf, 2nf, 3nf and BCNF in database. (2015, May 2). beginnersbook.com. https://beginnersbook.com/2015/05/normalization-in-dbms/

SQL – Sub queries. (n.d.). RxJS, ggplot2, Python Data Persistence, Caffe2, PyBrain, Python Data Access, H2O, Colab, Theano, Flutter, KNime, Mean.js, Weka, Solidity. https://www.tutorialspoint.com/sql/sql-sub-queries.htm

SQL commands. (n.d.). www.javatpoint.com. https://www.javatpoint.com/dbms-sql-command

SQL joins explained – Inner, left, right & full joins | Edureka. (2020, May 7). Edureka. https://www.edureka.co/blog/sql-joins-types

Tale, S. (2016). SQL: The ultimate beginners guide: Learn SQL today. Createspace Independent Publishing Platform.