This Assignment You Will Perform The Physical Design And Imp

N This Assignment You Will Perform Thephysicaldesign And Implementatio

This assignment involves performing the physical design and implementation of the Mom and Pop Johnson Video Store database using SQL Data Definition Language (DDL) and Data Manipulation Language (DML). You will create the database tables with appropriate integrity constraints, populate them with data, and execute various queries and updates. The process includes writing and testing SQL scripts, ensuring correct syntax, referential integrity, and producing output through SPOOL files that capture both SQL statements and their results.

Paper For Above instruction

The core objective of this assignment is to construct a robust and relational database for a hypothetical Mom and Pop Johnson Video Store, emphasizing the critical aspects of database design, implementation, data population, and data manipulation. This comprehensive process ensures that the database not only reflects accurate data relationships but also supports efficient data retrieval and updates, essential for operational integrity.

1. Creation of Database Tables with Constraints

The initial phase involves designing and creating the database tables using SQL DDL. Each table must include primary keys to uniquely identify records and foreign keys to establish relationships and maintain referential integrity. For example, tables such as Customers, Videos, Rentals, and Distributors should be created with appropriate data types and constraints such as NOT NULL, UNIQUE, and CHECK constraints to enforce data validity.

To ensure a smooth creation process, DROP TABLE statements should be incorporated at the beginning of the script to remove pre-existing tables, avoiding errors during re-creation. The CREATE TABLE statements should be followed by ALTER TABLE statements if additional constraints need to be added post-creation. Proper commenting and organization of the script will facilitate testing and debugging. All SQL commands must be syntactically correct and execute without errors, which can be verified through repeated testing.

2. Populating Tables with Data

Once tables are created, each must be populated with at least five valid rows of data. The INSERT statements should be executed in a sequence that respects referential integrity, meaning related data must exist prior to inserting dependent records. For instance, customers should be entered before records in the Rentals table that reference customer IDs. The INSERT statements, along with their execution outputs, should be documented in the SPOOL files, demonstrating successful data insertion without errors. Rerunning scripts until errors are resolved is crucial for ensuring data integrity and script accuracy.

3. Performing Queries and Data Updates

After populating the database, the next step involves executing specific SQL queries and updates to test data retrieval and modification capabilities. Each query should be written thoughtfully and tested thoroughly:

• Retrieve customer names, account numbers, and addresses: Sorted by account number, focusing on core contact details.
• Find videos rented in the last 30 days: Sorting rentals chronologically to analyze recent activity.
• List distributors: Including all distributor info, sorted alphabetically by company name.
• Update a customer’s marital name: Changing the maiden name to a married name, using the primary key in the WHERE clause to target a specific record.
• Delete a customer record: Removing an entry, again targeting a specific record with the primary key. Rollback statements can be used to undo updates or deletions for testing purposes.

Each SQL command should be documented with output results in the SPOOL files, demonstrating their successful execution. Using the SET ECHO ON command ensures that the SQL statements are captured along with their responses, providing clear documentation of the process.

4. Testing, Verification, and Submission

It is essential to thoroughly test each script, re-running and debugging until all commands execute without errors. Multiple attempts may be necessary to achieve error-free scripts. The final deliverables include the SPOOL files containing all executed commands and their outputs, which serve as proof of correct implementation and testing. Submission should only include these output files, not the original SQL script files.

Overall, this assignment emphasizes careful planning, rigorous testing, and detailed documentation of the database development process, culminating in a functional and reliable video store database system.

References

• Elmasri, R., & Navathe, S. B. (2015). Fundamentals of Database Systems (7th ed.). Pearson.