SQL | Homework Help

Project description
A basic database is in place. Look at the business rules and test them with some SQL. Look at the previous business rules developed and design some SQL queries to test

them. Set up a test plan. List the rule, the SQL that was written, and the results. The business rules are: Tenants arrange maintenance requests online and tenants can

pay rent online. Attached is an example of what the results should look like.

USE [Master]
GO
CREATE DATABASE [LEASE] ON PRIMARY
(NAME = N’Lease’, FILENAME = N’FSASQLDBlease.mdf’,
SIZE = 4GB, MAXSIZE = 10GB, FILEGROWTH = 2GB)
LOG ON
(NAME = N’Lease_log’, FILENAME = N’FSASQLDBlease_log.ldf’,
SIZE = 2GB, MAXSIZE = 3GB, FILEGROWTH = 10%)
GO
CREATE TABLE dbo.Tenant Lease Agreement (
TenantLeaseKey        CHAR(10),           Primary Key,
LeaseStartDate    DATE,            Not Null,
LeaseEnIDate    DATE,            Not Null,
Deposit    DECIMAL(15, 2),
RentAmount    DECIMAL(15, 2),
LateFees    DECIMAL(15, 2),
ApartmentKey             CHAR(15),        Foreign Key,
TenantKey            CHAR(20),        Foreign Key,
);

CREATE TABLE dbo. Building(
BuildingKey    CHAR(10),        Primary Key,
BuildingName    CHAR(30),    Not Null
BuldingAIDress          CHAR(30),     Not Null

);

CREATE TABLE dbo. Manager(

ManagerKey            CHAR(10),Primary Key
ManagerLastName                  CHAR(50),        Not Null
ManagerFirstName        CHAR(50),         Not Null
BuildingKey            CHAR(10),         Foreign Key

);

CREATE TABLE dbo.Tenant(

TenantKey            CHAR(20),        Primary Key
TenantLastName    CHAR(50),
TenantFirstName    CHAR(50),
TenantEmail_AIDress    CHAR(30),
TenantPhoneNumber             NUMERIC (20),
);

CREATE TABLE dbo.Rent Revenue(

RentRevenueKey        CHAR10),        Primary Key
RentAmount    DECIMAL(15, 2),
RentDueDate        DATE,
LateFeeAmount                       DECIMAL (15, 2),
TenantLeaseKey    CHAR(30),        Foreign Key
);

CREATE TABLE dbo.  Apartment Maintenance(
ApartmentMaintenanceKey    CHAR(10),        Primary Key
RequestDate            DATE,
MaintenanceType    CHAR(30),
Resolution            CHAR(30),
Resolution Date        DATE,
ApartmentKey    CHAR(30),        Foreign Key
);

CREATE TABLE dbo. Expense Type(
ExpenseTypeKey        CHAR(30),        Primary Key
Repair    CHAR(50),
Maintenance            CHAR(50),
Utilities                 CHAR(50),
Apartment Cleaning        CHAR(50),
Insurance                CHAR(50),
);
CREATE TABLE dbo. Expenses(
ExpenseCostKey        CHAR(30),    Primary Key
CostAmount    DECIMAL(15, 2),
ApartmentMaintenanceKey    CHAR(30),                 Foreign Key
ExpenseTypeKey    CHAR(30),        Foreign Key
);

ITC 4150, Database Design and Implementation

Unit VII €“ Westlake Research Hospital
Sample Answers to Scenario Questions
1. Two or three simple SELECTs with various WHERE criteria Rule to Test List all the patients of a given doctor List all the PatientKeys of patients whose depression

level is severe Means of Testing
SELECT * FROM Patient WHERE DoctorKey=’j303€² SELECT PatientKey FROM PatientMedicalHistory WHERE DepressionLevel=’Severe’

Expected Result 4 records 3 records

Result 4 3

2. Two or three queries using aggregate functions Rule to Test Count the number of patients in each drug group Count the number of patients per doctor Means of Testing
SELECT DrugKey, COUNT(PatientKey) FROM PatientGroup GROUP BY DrugKey SELECT DoctorKey, COUNT(PatientKey) FROM Patient GROUP BY DoctorKey

Expected Result 7 in group 12, 6 in group 2 2 or 3 per doctor

Result Success

Success

3. At least two queries that use joins Rule to Test Get the names of all patients that have diabetes Means of Testing
SELECT PatientLastName, Diabetes FROM Patient p INNER JOIN PatientMedicalHistory pmh ON p.PatientKey=pmh.PatientKey WHERE Diabetes = 1 SELECT PatientLastName,

AppointmentDate, BloodPressure, Pulse, DepressionStatus FROM Patient p INNER JOIN Appointment a ON p.PatientKey=a.PatientKey INNER JOIN AppointmentDetail

Expected Result 2

Result 2

Get patient name, appointment date, blood pressure , pulse and depression status for a particular patient

Johnson, 10/12/2010,110/70,7 7,Same

Success

ad ON a.AppointmentKey=ad.Appointme ntKey WHERE p.PatientKey=’JL200€²

4. Two or three INSERT statements Rule to Test AID a new appointment and appointment details Means of Testing
INSERT INTO Appointment(AppointmentKey, AppointmentDate, AppointmentTime,PatientKey, DoctorKey) VALUES(‘10101€²,’10/23/2010€²,’ 2:30 PM’, JL200€²,’PD33€²) INSERT INTO

AppointmentDetail(Appointment DetailKey, AppointmentKey, Bloodpressure, Pulse, PatientWeight, DepressionLevel, DoctorsNotes) VALUES(‘22020€²,’10101€²,’114/7

5€²,77,187,’Moderate’,’the patient seems to be making some progess’)

Expected Result Two new records

Result success

5. One or two UPDATEs and/or a DELETE Rule to Test Change a patient’s doctor Change a patient’s phone number Means of Testing
UPDATE Patient SET DoctorKey=’jj01€² WHERE PatientKey=’JL200€² UPDATE Patient SET PatientPhone=’2065551245€² WHERE PatientKey=’JL200€²

Expected Result 1 record changed 1 record changed

Result 1 record changed 1 record changed

General Guidelines for this Assignment
The queries should be a part of a testing scenario. Each query should validate that the database fulfills a requirement or business rule. The testing, however, is not

expected to be complete or comprehensive in any way. The following rubric for this unit assignment should also guide you on the level of detail and the content

expected. Contact your instructor if you have questions.

Poor Incomplete or malformed SQL. Queries don’t aIDress requirements or business rules. No testing format

OK Uses testing format. Queries mostly correct. Queries mostly aIDress requirements and business rules

Good Uses testing format. All queries well-formed and directed at requirements and business rules