Physical Design Introduction This is a work project that involves designing and translating data into an actual database. It involves review of diagram for the database to ensure that the design is normalized and complete (Bambara & Allen, 1999). Creation of database in SQL Server and tables in the new database, selecting appropriate data types for the columns, setting a primary key for each table, and setting allow nulls as appropriate also create a database diagram and relationships among tables (Dewson, 2012). Finally, provide some sample data to each table.
Review Design
Review of diagram for the database
Creating database in SQL Server
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
Creating Tables
CREATE TABLE dbo. Lease Agreement
(
Lease_ID int, Primary Key,
Lease_Start_Date DATE, Not Null,
Lease_End_Date DATE, Not Null,
Deposit DECIMAL (15, 2),
Rent_Amount DECIMAL (15, 2),
Late_Fees DECIMAL (15, 2),
Apartment_ID int, Foreign Key,
Tenant_ID int, Foreign Key,
);
CREATE TABLE dbo. Building
(
Building_ID int, Primary Key,
Plot_No VARCHAR (255),
Building_Status VARCHAR (255), Not Null
Building_NameVARCHAR (255), Not Null
Bulding_Address VARCHAR (255), Not Null
Lease_ID int, Foreign Key
);
CREATE TABLE dbo. Manager
(
Manager_ID int, Primary Key
Manager_LastName VARCHAR (255), Not Null
Manager_FirstName VARCHAR (255), Not Null
Building_IDint, Foreign Key
Manager_qualification VARCHAR (255), Not Null
Manager_Expertise VARCHAR (255), Not Null
);
CREATE TABLE dbo. Tenant
(
Tenant_ID int, Primary Key
Tenant_LastName VARCHAR (255),
Tenant_FirstName VARCHAR (255),
Tenant_Email_Address VARCHAR (255),
);
CREATE TABLE dbo. Rent
(
Rent_ID int, Primary Key
RentAmount DECIMAL (15, 2),
RentDueDateDATE,
Lease_ID int, Foreign Key
);
CREATE TABLE dbo. Maintenance and Repair
(
Maintenance_Request_ID int, Primary Key
DateDATE,
Description VARCHAR (255),
Cost DECIMAL (15, 2),
Resolution VARCHAR (255),
Resolution Date DATE,
Tenant_ID int, Foreign Key
);
CREATE TABLE dbo. Expense Type
(
Expenses_Type_ID int, Primary Key
Repair VARCHAR (255),
Maintenance VARCHAR (255),
Utilities VARCHAR (255),
Apartment Cleaning VARCHAR (255),
InsuranceVARCHAR (255),
);
CREATE TABLE dbo. Expenses
(
Expense_ID int, Primary Key
Cost DECIMAL (15, 2),
Expense Management VARCHAR (255),
Expense_Type_ID int,
);
Conclusion
The design has been implemented by SQL Server 12 to create database and tables. The design shows knowledge acquired during coursework. The project involved designing and translating data into an actual database. Running the database and SQL tables will give proper results that comply with project’s objective.
References
Bambara, J. J., & Allen, P. R. (1999). SQL Server reference. Indianapolis, Ind: New Riders.
Dewson, R. (2012). Beginning SQL Server 2012 for developers. New York: Apress.
Powell, G. (2006). Beginning database design. Indianapolis, Ind: Wiley.