This Week You Will Design A Model Schema Table Structure ✓ Solved
For This Week You Will Design A Model Schema Table Structure Based O
For this week you will design a model schema (table structure) based on the data model shown below: First Table: Employee entity: EmployeeID, Employee First Name, Employee Last Name, Employee Department, Employee Position, Employee Email, Employee Phone. Second Table: Project Entity: ProjectID, Project Name, Project Description, Project Start Date, Project End Date, Project Budget Code. Third Table: Department: Department Number, Department Title, Location. Fourth Table: Assignment: ProjectID, EmployeeID, Hours Worked. You will design the physical database diagram consisting of four tables using the database design language (DBDL) notation and table composition format that must include table name, attributes, data types, validation criteria. This assignment does not follow APA format expectations for citations and references.
Sample Paper For Above instruction
Introduction
Designing a robust and efficient database schema is essential for effective data management and retrieval in any organizational system. In this assignment, the goal is to develop a physical database diagram comprising four interconnected tables: Employee, Project, Department, and Assignment, based on predefined data entities and their attributes. These tables will be designed using the Database Design Language (DBDL) notation, explicitly detailing table names, attributes, data types, and validation criteria to ensure data integrity, consistency, and clarity. The schema aims to facilitate seamless management of employee information, project details, departmental data, and assignment records, contributing to an integrated enterprise information system.
Designing the Employee Table
The Employee table serves as the cornerstone by storing personal and professional information about each employee. The primary key is EmployeeID, uniquely identifying each employee. Other attributes include First Name, Last Name, Department, Position, Email, and Phone Number. For data types, I recommend the following structure:
Employee (EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
Department VARCHAR(50) NOT NULL,
Position VARCHAR(50),
Email VARCHAR(100) UNIQUE NOT NULL,
Phone VARCHAR(15)
)
Validation criteria ensure that mandatory fields like EmployeeID, First Name, Last Name, Department, and Email are not null. The Email field is unique to prevent duplicates, and the Phone number field is constrained to a standard format.
Designing the Project Table
The Project table catalogues project-specific information, crucial for tracking ongoing and completed initiatives. The primary key is ProjectID. Additional attributes include Name, Description, Start Date, End Date, and Budget Code. The data types are chosen as:
Project (ProjectID INT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Description TEXT,
StartDate DATE NOT NULL,
EndDate DATE,
BudgetCode VARCHAR(20)
)
The Start Date and End Date fields are validated as DATE types, with the possibility to add constraints such as End Date >= Start Date.
Designing the Department Table
The Department table organizes organizational units, capturing department identification and location. Its primary key is Department Number. Attributes include Title and Location, with data types as follows:
Department (DepartmentNumber INT PRIMARY KEY,
Title VARCHAR(50) NOT NULL,
Location VARCHAR(100)
)
Validation ensures the Department Number and Title are not null, supporting clear organizational structure.
Designing the Assignment Table
The Assignment table links employees to projects, recording hours worked. It references EmployeeID and ProjectID as foreign keys, forming relationships with the Employee and Project tables. Additionally, it records Hours Worked, validated as a decimal or float to accommodate fractional hours. Its structure is:
Assignment (ProjectID INT,
EmployeeID INT,
HoursWorked DECIMAL(5,2),
PRIMARY KEY (ProjectID, EmployeeID),
FOREIGN KEY (ProjectID) REFERENCES Project(ProjectID),
FOREIGN KEY (EmployeeID) REFERENCES Employee(EmployeeID)
)
Validation criteria include Hours Worked being non-negative and within realistic bounds.
Conclusion
This database schema design integrates four essential tables, establishing relationships via foreign keys to support organizational processes such as employee management, project tracking, departmental organization, and assignment monitoring. The careful selection of data types and validation rules ensures data consistency, accuracy, and integrity—fundamental qualities for reliable database systems. The physical schema, represented in DBDL notation, provides a clear blueprint for implementation and future scalability.
References
- Elmasri, R., & Navathe, S. B. (2015). Fundamentals of Database Systems (7th ed.). Pearson.
- Database Systems: A Practical Approach to Design, Implementation, and Management (6th ed.). Pearson.