Description
In this assignment we will alter some tables to add check constraints and we will add indexes to speed up searches. Also we will insert some data. It is very important that you do all the changes and inserts in this assignment. Otherwise the Insert script I give you to add the remaining data will fail.
To Do
Changes
1. Alter the table Apartment. First add a check constraint on the field Bedrooms. Make it so you can only enter a number between 1 and 3 (inclusive of 1 and 3)
2. Alter the table MaintenanceReqDetail to add a column DateInspected, DateTime
One of our business rules as that each apartment is leased to only one tenant (that is only one person signs each lease.) But the manager realizes that he needs to keep track of the other people in the apartment. He is going to relate this table through the Lease.
3. Add another table called "TenantRoomates." It should have this structure
| Column | DataType | Constraints |
|---|---|---|
| RoomMateID | Int identity (1,1,) | Primary Key |
| LeaseNumber | char(10) | foreign Key |
| RmLastName | varchar(30) | not null |
| RmFirstName | varchar(20) | none |
| BeginDate | DateTime | not null |
| MoveOutDate | varchar(30) | null |
Indexes
4. Add and index for every foreign key
5. Additionally, add an index for LastName under Tenant
Inserts
We are going to start adding data. I will provide a script with the majority of inserts by next class.
The apartment building has three floors. Each floor has six Apartments. The apartments are numbered 101 through 106 on the first floor 201 through 206 on the second etc.101 and 104 are one bedroom, 102 and105 are 3 bedroom and 103 and 106 are two bedroom. The same pattern applies to all three floors. Only apartments 301 through 303 have a view. (The view increases the rent by $100.00)
6. Insert all 18 apartments into the database
7. Now we will enter a single tenant. His last name is "Martins." His first name is "Thomas." His phone is "206-555-1234." (Don't enter the dashes.) He has no roomates. The key field is made by taking the first letter of the first name and up to 7 letters of the last name and a number usually 01. (tmartins01)
Now Enter his lease.
8. The lease number consists of the startDate (minus the slashes and with 06 only for the year) and the aptnumber (10506201). The startdate is 1/05/2006. The apartment is 201. The Deposit amount is $1150.00. The end Date is 6 months from the startDate. The Rent amount is $850.
To turn in
Turn in the code