Using Your Week 1 IP As A Starting Point To Extend The Desig
Using Your Week 1 Ip As A Starting Point Extend The Design To Accommo
Using your Week 1 IP as a starting point, extend the design to accommodate degree programs. The new design should incorporate the following functionalities: A degree has a name and description. What degree is the student working towards? A student can only work on one degree at a time. What classes are necessary to obtain a specific degree?
Provide the DDL script to add the tables for the new design changes. Include an updated diagram that shows all the tables in the system. Write the DML script to insert 3 test records in each of the tables in the system. This data will be necessary to write the queries in the next assignment. Copy and paste the work into your Key Assignment document and include screen shots of each step, describe what you did for each step and paste in the actual SQL text used to perform each step.
Paper For Above instruction
To effectively extend the existing database design based on the initial Week 1 implementation and incorporate degree programs, a systematic approach involving database schema modification, data population, and diagram updating is essential. This process ensures that the new functionalities—tracking degrees and their required classes—are seamlessly integrated into the current system.
Step 1: Analyzing the Existing System
Before adding new tables, review the existing database schema to understand how entities such as students, classes, and enrollments are structured, which will inform the integration of degree-related data. Typically, existing schemas might include tables like 'Students', 'Classes', 'Enrollments', etc.
Step 2: Designing New Tables
The new functionalities require two primary pieces of information:
- Degrees: Each degree has a name and description.
- Degree Requirements: Each degree requires certain classes.
Given these, the following tables will be created:
1. `Degrees`
- `DegreeID` (Primary Key)
- `DegreeName`
- `DegreeDescription`
2. `DegreeClasses`
- `DegreeID` (Foreign Key)
- `ClassID` (Foreign Key)
3. Update the `Students` table to include a reference to the degree the student is working towards, assuming each student can only work on one degree at a time:
- Add `DegreeID` (Foreign Key) to `Students`
Step 3: Creating the Tables with DDL Scripts
```sql
-- Create Degrees table
CREATE TABLE Degrees (
DegreeID INT PRIMARY KEY AUTO_INCREMENT,
DegreeName VARCHAR(100) NOT NULL,
DegreeDescription TEXT
);
-- Create DegreeClasses table to associate degrees with classes
CREATE TABLE DegreeClasses (
DegreeID INT,
ClassID INT,
PRIMARY KEY (DegreeID, ClassID),
FOREIGN KEY (DegreeID) REFERENCES Degrees(DegreeID),
FOREIGN KEY (ClassID) REFERENCES Classes(ClassID)
);
-- Alter Students table to include DegreeID
ALTER TABLE Students
ADD COLUMN DegreeID INT,
ADD FOREIGN KEY (DegreeID) REFERENCES Degrees(DegreeID);
```
Step 4: Updating the Diagram
Update the Entity-Relationship diagram to include the new `Degrees` and `DegreeClasses` tables and the foreign key relationship from `Students` to `Degrees`. This visualization aids in comprehending table relationships and ensuring referential integrity.
Step 5: Inserting Test Data with DML Scripts
```sql
-- Insert test records into Degrees
INSERT INTO Degrees (DegreeName, DegreeDescription) VALUES
('Bachelor of Science in Computer Science', 'Undergraduate degree focusing on computing fundamentals.'),
('Bachelor of Arts in History', 'Undergraduate degree focusing on historical studies.'),
('Master of Business Administration', 'Graduate degree focusing on business management.');
-- Insert test records into Classes
INSERT INTO Classes (ClassName, ClassDescription) VALUES
('Introduction to Programming', 'Basics of programming in Python.'),
('World History', 'Overview of world historical events.'),
('Business Strategy', 'Advanced topics in strategic management.');
-- Associate degrees with classes
INSERT INTO DegreeClasses (DegreeID, ClassID) VALUES
(1, 1), -- Bachelor of Science in CS requires Introduction to Programming
(2, 2), -- Bachelor of Arts in History requires World History
(3, 3); -- MBA requires Business Strategy
-- Insert test students and assign them to degrees
INSERT INTO Students (StudentName, Major, DegreeID) VALUES
('Alice Johnson', 'Computer Science', 1),
('Bob Smith', 'History', 2),
('Carol Williams', 'Business', 3);
```
Step 6: Documentation and Screenshots
For comprehensive documentation, capture screenshots of:
- Schema diagrams before and after changes.
- SQL scripts run in the database management system.
- Query results from inserted test data.
Describe each step explicitly:
- How the existing schema was reviewed.
- The reasoning behind table and column choices.
- The SQL commands used to create tables and insert data.
- The modifications made to the diagram to include new tables.
Conclusion
By extending the database schema to include degree-related tables and updating the `Students` table, the system now supports tracking students' degree programs and their required classes. Proper documentation, including SQL scripts and updated diagrams, ensures clarity and facilitates future enhancements.
References
- Aho, A. V., Hopcroft, J. E., & Ullman, J. D. (1983). Data Structures and Algorithms. Addison-Wesley.
- Database Systems: A Practical Approach to Design, Implementation, and Management. Pearson.