SP​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ Yugmi

Tutorial 3 – Data Dictionary

Name: Yugmi Khakhar​
Roll No.: 44​
Enrollment No.: 23SOECE11103​
Division: A

1. Online Shopping System – Data Dictionary

Data Dictionary 1: User Account Details

Field Name Description Data Type Size Constraints

User_ID Unique ID Integer 10 Primary
for each Key, Auto
user Increment
Username User's login Varchar 50 Unique, Not
name Null
Password User's login Varchar 100 Not Null
password
Join_Date Account DateTime - Not Null
creation
date

Data Dictionary 2: Cart Information

Field Name Description Data Type Size Constraints

Cart_ID Unique Integer 10 Primary
cart ID Key, Auto
Increment
User_ID Refers to Integer 10 Foreign Key
SP​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ Yugmi

the user
Created_On Date and DateTime - Not Null
time cart
was created
Total_Items Number of Integer - Default 0
items in the
cart

Data Dictionary 3: Shipping Address

Field Name Description Data Type Size Constraints

Address_ID Unique Integer 10 Primary
address ID Key, Auto
Increment
User_ID Refers to Integer 10 Foreign Key
the user
Address_Li Street Varchar 200 Not Null
ne address
City City name Varchar 50 Not Null
Pincode Postal code Varchar 6 Not Null

2. Hospital Management System – Data

Dictionary
Data Dictionary 4: Room Allocation
SP​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ Yugmi

Field Name Description Data Type Size Constraints

Room_ID Unique ID Integer 10 Primary
for each Key, Auto
room Increment
Room_Type Type of Varchar 20 Not Null
room (ICU,
General,
Private)
Capacity Number of Integer - Not Null
beds in the
room
Available_B Number of Integer - Not Null
eds currently
available
beds

Data Dictionary 5: Staff Details

Field Name Description Data Type Size Constraints

Staff_ID Unique Integer 10 Primary
staff Key, Auto
member ID Increment
Name Full name Varchar 100 Not Null
of the staff
Roll Role in Varchar 50 Not Null
hospital
(Nurse,
Admin, etc.)
Contact Contact Varchar 15 Not Null
number
SP​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ Yugmi

Data Dictionary 6: Medical Records

Field Name Description Data Type Size Constraints

Record_ID Unique Integer 10 Primary
medical Key, Auto
record ID Increment
Patient_ID Refers to Integer 10 Foreign Key
patient
Diagnosis Description Varchar 200 Not Null
of
diagnosis
Treatment Treatment Varchar 200 Not Null
Details
Record_Dat Date of Date - Not Null
e record

3. Library Management System – Data Dictionary

Data Dictionary 7: Library Branch Details

Field Name Description Data Type Size Constraints

Branch_ID Unique ID Integer 10 Primary
of the Key, Auto
library Increment
branch
Location Address or Varchar 150 Not Null
area of the
SP​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ Yugmi

branch
Contact_No Phone Varchar 15 Not Null
number of
the branch

Data Dictionary 8: Book Reviews

Field Name Description Data Type Size Constraints

Review_ID Unique Integer 10 Primary
review ID Key, Auto
Increment
Book_ID Refers to Integer 10 Foreign Key
the
reviewed
book
Member_ID Refers to Integer 10 Foreign Key
the
member
Rating Rating out Integer - Check
of 5 (Rating <= 5)
Comment Review Varchar 200 Optional
Comment

Data Dictionary 9: Fine Collection

Field Name Description Data Type Size Constraints

Fine_ID Unique ID Integer 10 Primary
for fine Key, Auto
entry Increment
Member_ID Refers to Integer 10 Foreign Key
SP​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ Yugmi

the
member
Amount Refers to Integer 10 Not Null
the amount
Paid_Status Whether Varchar 10 Default:
fine is paid ‘Unpaid’
or not
Due_Date Date by Date - Not Null
which
payment is
due