Practice Lab 1: Validate The Sample Database I ✓ Solved

Practice Lab 1 Validate The Sample Database I

Practice Lab #1 - Validate the sample database is populated with STUDENT tables and data. Login to the DaaS environment and connect to Oracle for DBST 651 using the DBST_USER account. Before proceeding with the lab, you must validate that the COURSE, ENROLLMENT, GRADE, GRADE_CONVERSION, GRADE_TYPE, GRADE_TYPE_WEIGHT, INSTRUCTOR, SECTION, STUDENT, and ZIPCODE tables exist. Validate tables exist using Oracle SQL Developer: Expand the ‘Tables’ node under the connection you made and you should see the 10 tables from the STUDENT schema. Validate tables exist using SQL Developer with the following query: SELECT table_name FROM user_tables; If the 10 STUDENT tables do not exist, navigate to Course Content > Hands-on Resources > StudentSchema within the classroom using a browser in the DaaS environment.

Extract the contents of the zip file and follow the instructions in the Readme.pdf to create the STUDENT schema. For each STUDENT table, execute a SELECT query displaying a count of the total number of rows in that table by replacing <table_name> with the name of the STUDENT table. SELECT COUNT() AS "Count of <table_name>" FROM <table_name>; Example: SELECT COUNT() AS "Count of COURSE" FROM COURSE;

Deliverable: Submit a single Word document that contains the query executed and output text or output screenshots showing the results for all 10 STUDENT tables. Ensure that both the column alias (i.e., ‘Count of <table_name>’) and the count itself is visible on all 10 results. Notify the course TA if these tables were not available to you.

Sample Paper For Above instruction

Introduction

The process of validating database tables is essential in ensuring data integrity and completeness within a specific schema. This is particularly critical in educational environments where accurate student and course information supports decision-making and reporting. The objective of this practice lab is to verify the existence of key tables within the sample database provided in the DaaS environment and to quantify the data contained within these tables.

Database Environment and Preparation

The first step involves logging into the DaaS environment, which provides a cloud-based platform for database management. Using Oracle SQL Developer, a common tool for database development and management, the user must establish a connection to the Oracle database for course DBST 651, utilizing the provided username, DBST_USER. Once connected, the user should expand the ‘Tables’ node to visually confirm the presence of the required tables: COURSE, ENROLLMENT, GRADE, GRADE_CONVERSION, GRADE_TYPE, GRADE_TYPE_WEIGHT, INSTRUCTOR, SECTION, STUDENT, and ZIPCODE.

Validation of Table Existence

To systematically verify table existence, the SQL query SELECT table_name FROM user_tables; can be executed. This query returns all table names owned by the current user, allowing the user to cross-check with the expected list. If any of these tables are missing, the instructions recommend navigating to the specified course content resources to locate the necessary schema creation scripts or instructions for creating the tables based on the provided zip file and Readme.pdf documentation.

Populating the Schema

After extracting contents from the provided zip file and executing the setup instructions outlined in Readme.pdf, the user creates the STUDENT schema, which includes the essential tables. Accurate creation and population of this schema are prerequisites for proceeding with data validation and counting.

Counting Rows in Each Table

For a comprehensive review of data volume, a COUNT query is executed for each table, formatted as:

SELECT COUNT(*) AS "Count of <table_name>" FROM <table_name>;

This process is repeated for all 10 tables, with the results documented in the Word document submission. The output should clearly display the count along with the corresponding column alias, ensuring the evaluators can interpret the data accurately.

Conclusion

Validating the existence and populating data within critical database tables forms an essential foundation for further data analysis and application development. Through systematic checks using SQL queries and visual inspections in SQL Developer, one can confirm the integrity and completeness of the database schema, facilitating reliable subsequent operations.

References

  • Oracle Corporation. (2020). Oracle SQL Developer User's Guide. Oracle Documentation.
  • Coronel, C., & Morris, S. (2015). Database Systems: Design, Implementation, & Management. Cengage Learning.
  • Elmasri, R., & Navathe, S. B. (2015). Fundamentals of Database Systems. Pearson.
  • Hoffer, J. A., Venkataraman, R., & Toptygin, I. (2016). Modern Database Management. Pearson.
  • Date, C. J. (2004). An Introduction to Database Systems. Pearson Education.
  • Rob, P., & Coronel, C. (2007). Database Systems: Design, Implementation, & Management. Course Technology.
  • Kroenke, D. M., & Auer, D. J. (2018). Database Concepts. Pearson.
  • Silberschatz, A., Korth, H. F., & Sudarshan, S. (2011). Database System Concepts. McGraw-Hill.
  • Stonebraker, M., & Hellerstein, J. M. (2005). What Goes Around Comes Around: Architectures of Data Management Systems. Communications of the ACM, 48(5), 55-58.
  • Abiteboul, S., Hull, R., & Vianu, V. (1995). Foundations of Databases. Addison-Wesley.

Related Assignments