SQL Queries - Assignment 1
Assignment 1
--------------------------
Create the following tables using SQL Server Management Studio.
Table Name : flatMaster
About this table:
The flatMaster table is a master table which holds all the details related to all the flats in the apartment. This table has got flatNo as primary key as there is no duplicate flat numbers (and no blank) in the apartment. Flat numbers are written/captured in such a way that they give meaning to block number and flat number. A101 gives the meaning as , this belongs to block 'A' and then number of the flat is 101.
Field Name | Field Type | Constraints (if any) | Description |
flatNo | varchar(10) | PRIMARY KEY | This field contain the flat number. This would an alphanumeric value. For example : A303 (means block A and flat no. 303) |
floorNo | smallint | NA | Floor number. For example : 1 , 2, 3 |
block | varchar(20) | NA | This is the name of the block. For example : 1 , ‘A’, etc. |
areasqft | float | NA | Example : 1100 etc. |
comments | varchar(100) | NA | Any additional comments |
Table Name : residents
About this table:
Table residents is the one which holds all the residents details. This table contains almost all the information related to the residents in the apartment society.
|
Field
Name |
Field
Type |
Constraints
(if any) |
Description |
|
esidentID |
smallint |
Primary
Key |
This is
the ID of residents. Example: 1,2 ,3 etc. |
|
residentName |
Varchar(80) |
NOT
NULL |
Name of
the resident. Example : John Stone |
|
residentType |
Char(1) |
“O” or “T” |
Residents
may be “Owners” or “Tenants” |
|
flatNo |
Varchar(10) |
Foreign
key to flatMaster(flatNo) |
Stores
the flat number of the resident. |
|
phone |
varchar(10) |
NA |
Phone
number of the resident |
|
DOB |
datetime |
Age at
least 18 Years. (Difference of DOB and the current data should not be less
than 18) |
Date of
Birth of the resident |
|
email |
varchar(50) |
NA |
Email
of the resident. |
|
lastEditAt |
datetime |
DEFAULT
is current Date |
Last
edit date |
|
lastEditComments |
varchar(50) |
NA |
Last
edit comments
|
About this table:
All the residents are users as well. They will login into the apartment system. This table has got all the users details. Residents will have userID & password (i.e. pwd) using which they can login into the system. The moment user goes out or no more the part of the apartment , isActive field can be turned into 'N' instead of 'Y'.
Field Name | Field Type | Constraints (if any) | Description |
userID | smallint | Foreign key reference to residentID of residents table | Holds the userID of the users login to the system |
userName | varchar(50) | Primary Key | User Name of the users. This is unique in nature. |
pwd | varchar(50) | NA | Holds password |
isActive | char(1) | Value should be ‘Y’ or ‘N’ | Holds yes (‘Y’) or No (‘N’). |
dtCreated | datetime | Default Current Date | Holds the date of creation |
lastEditAt | datetime | NA | Holds the data when last edit took place |
lastEditComments | varchar(100) | NA | Holds the last edit comments. |
About this table:
Household, working staffs are maids. This table is the master table for the maids. This holds all the maid details. This is an important table as maids or househelps are important and lifeline of the residents residing.
|
Field
Name |
Field
Type |
Constraints
(if any) |
Description |
|
maidID |
Int |
PRIMARY KEY |
Holds the maid ID. The values can be 1, 2, 3… |
|
maidName |
varchar(100) |
NOT NULL |
Holds the name of the maid. Example : Sumit Agarwal |
|
sex |
Varchar(10) |
CHECK = “Male” “Female”, “Undefined” |
Sex can be either of the 3 values. |
|
experience |
vharchar(200) |
NA |
Names of the skills (comma separated). Example : Cleaning,
Kitchen Work , Baby sitting |
|
isActive |
char(1) |
Value must be either ‘Y’ or ‘N’ |
Holds the value ‘Y’ or ‘N’. If the maid is active or not |
|
dtJoined |
Datetime |
NA |
Date when the maid joined the apartment. |
About this table:
As the name suggests , this table will hold the link details of a maid and the flats. One maid can have multiple flat association. This also maintains the ratings of the maids and helpful for others to take decision about which maid is to be selected for their household activities.
Field Name | Field Type | Constraints (if any) | Description |
flatNo | varchar(10 ) | Foreign Key to FlatMaster(FlatNo) | Holds the flat number where the maid works |
maidID | smallint | Foreign key to maidMaster(maidID) | Holds the id of the main. For example : 1,2 etc. |
workingSince | datetime | NA | Hold the date since when the maid is working with flats |
ratings | smallint | CHECK constraints where the value of ratings should range from 1 to 5 | Holds the ratings of the main. The value can be 1 to 5. |
comments | varchar(100) | NA | Comments if any. For example : Good behaviour etc. |
Comments
Post a Comment