Database Design Midterm Exam Spring 2020

Database Design Mid Term Examspring 2020name

Database Design Mid Term Exam Spring 2020 Name: ____________________________

1. What is a data model? A. method of storing files on a disk drive B. simple representation of complex real-world data structures C. name of system for designing software D. method of designing invoices for customers

2. A Relationship Database system consists of 3 parts: a client front end for sending information to a command processor, a middle tier that interprets user commands, and a management frame work for storing, organizing and securing data. a. True b. False

3. What are the 3 components of a table: A. Row, column, value B. Row, top, bottom C. Column, row, top D. Top, middle, end

4. What does the column represent in a table? a. Attribute of the table records b. A complete record in the table c. The system log from the database d. A list of database tables

5. What does a row in the table represent? a. A complete data record b. List of system logs c. A list of file systems on database server d. The primary keys from all the tables

6. Which of the following is an example of data definition language (DDL)? a. UPDATE b. V$SYSLOG c. CREATE d. DETAIN

7. Which of the following is an example of data manipulation language (DML)? A. SELECT B. ABORT C. GRANT D. REVOKE

8. A _______ key is an attribute that uniquely identifies a record in a table.

9. A _______ key is an attribute that is a primary key in one table and is used as a reference in a second table to establish a relationship between the two tables.

10. When running a ‘SELECT’ join, what is returned from the table: A. ROW B. Column C. single attribute D. all tables in the database

11. When running a ‘PROJECT’ join, what is returned from the table: A. COLUMN B. ROW C. Single Attribute D. a list of tables in the database

12. What are the 3 types of relationships commonly shown on an entity relationship diagram? A. 1 to 1 B. 1 to Many C. Many to Many D. All the above E. None of the above

13. What is an entity relationship diagram (ERD)? A. graphical representation of all entities in a database and how the entities are related b. list of the log files in the database. C. list of all the tablespace names in a database D. A diagram that shows how data is written to a physical disk drive.

14. The definition of an attribute in a table that has no value is: A. ZERO b. NULL c. ZILTCH D. NONE

15. A ____________ attribute can either be stored on retrieve on an ad hoc basis.

16. Briefly describe the advantages and disadvantages of storing a derived attribute?

17. A database can process many types of data classifications. Which of the following is not a data classification or architecture that databases can process: A. Structured B. Semi-structured C. undelimited D. Unstructured

18. The process by which functional/partial dependency and transitive dependency is removed from a database table is called: a. sharding b. normalization c. defragmentation d. reallocation

19. A type of database that is used for decision making and business intelligence is what type of database: a. general purpose database b. content management database c. analytical database d. cloud database

20. All of the following are disadvantages to database systems except which of the choices below: a. Vendor dependence b. increased security c. increased management complexity d. Frequent upgrade cycles

Paper For Above instruction

Database design is a fundamental aspect of information technology that involves creating a structured framework for organizing, storing, and managing data efficiently. A comprehensive understanding of data models, relational components, keys, diagrammatic representations, and normalization processes is essential for designing effective database systems that support business operations, analytics, and decision-making processes.

At its core, a data model provides a simplified, abstract representation of complex real-world data structures, enabling designers to conceptualize how data entities interact within a system (Date, 2004). It serves as the blueprint for the entire database architecture, ensuring that data is logically stored and easily accessible. Data models range from high-level conceptual frameworks to detailed physical schemas, with the relational model being one of the most popular due to its simplicity and adaptability (Elmasri & Navathe, 2015).

A relational database system consists of three primary components: the front-end application or client that interfaces with users, the middle-tier or processing layer that interprets commands, and the backend management system that handles storage, organization, and security of data (Coronel & Morris, 2016). The core of the relational model involves by tables, which organize data into rows (records) and columns (attributes). The interrelations among tables are established through keys, fundamental in maintaining data integrity and enabling complex queries.

The components of a typical table include rows, columns, and values (Elmasri & Navathe, 2015). Each column represents an attribute of the data, such as customer name or product ID, providing the descriptive facets of the entity. Conversely, each row encapsulates a complete data record, storing specific information about a single entity or event, such as a particular customer or transaction (Elmasri & Navathe, 2015). This tabular structure supports various operations like data retrieval, insertion, updating, and deletion.

In relational databases, the distinction between keys is vital. A primary key uniquely identifies a record within the table, essential for ensuring entity integrity. It must have a unique, non-null value for each record (Ramezani & Soleimani, 2020). Foreign keys, on the other hand, are attributes used as references to primary keys in other tables, establishing relationships between tables. This mechanism enforces referential integrity, maintaining consistent and valid connections among related data entities (Ullman & Widom, 2008).

Data Definition Language (DDL) and Data Manipulation Language (DML) are fundamental components of SQL used to define and manipulate data structures. DDL commands such as CREATE are used to create tables and schemas, while DML commands like SELECT are employed for retrieving and manipulating data within those structures (Date, 2004). Proper utilization of these commands facilitates the development and maintenance of robust database systems.

Entity-Relationship Diagrams (ERDs) visually depict the relationships between entities in a database. Entities represent objects or concepts, while relationships define how entities are associated. The primary relationships identified on ERDs include one-to-one, one-to-many, and many-to-many connections, which shape the logical structure of the database and influence normalization procedures (Chen, 1976).

Normalization is a critical process in database design that involves removing functional, partial, and transitive dependencies to reduce redundancy and improve data integrity (Codd, 1970). Through various normal forms, normalization ensures that the database conforms to principles that minimize anomalies and facilitate efficient data management. It is a cornerstone in developing well-structured relational databases.

Database architectures vary based on specific use cases. Analytical databases, designed for decision support and business intelligence, are optimized for complex queries and large-scale analytical processing, often employing denormalization techniques for performance (Khatri, 2013). Contrarily, general-purpose databases are robust, versatile, and capable of handling diverse data types and workloads, including transaction processing and data warehousing.

Despite the advantages of database systems, they also come with disadvantages. Vendor dependence can limit flexibility and lead to vendor lock-in. Increased security is necessary but challenging, and managing complex database environments requires skilled personnel (Hellerstein, 2008). Frequent upgrades and maintenance cycles, although necessary, can disrupt operations and incur costs.

In conclusion, database design encompasses a broad array of principles, including data modeling, key identification, normalization, and diagrammatic representation. Proper understanding and application of these principles are vital for creating databases that are efficient, reliable, and scalable to meet evolving organizational needs.

References

  • Codd, E. F. (1970). A relational model for large shared data banks. Communications of the ACM, 13(6), 377-387.
  • Chen, P. P. (1976). The entity-relationship model—toward a unified view of data. ACM Transactions on Database Systems, 1(1), 9-36.
  • Coronel, C., & Morris, S. (2016). Database Systems: Design, Implementation, & Management (12th ed.). Cengage Learning.
  • Elmasri, R., & Navathe, S. B. (2015). Fundamentals of Database Systems (7th ed.). Pearson.
  • Hellerstein, J. M. (2008). Quantitative Data Analysis in Database Systems. ACM SIGMOD Record, 37(4), 5-13.
  • Khatri, V. (2013). Data Warehousing for Business Intelligence. Morgan Kaufmann.
  • Ramezani, M., & Soleimani, M. (2020). Database Keys and Their Impact on Data Integrity. Journal of Information Systems, 34(2), 45-58.
  • Ullman, J. D., & Widom, J. (2008). A First Course in Database Systems (3rd ed.). Pearson.
  • Date, C. J. (2004). An Introduction to Database Systems (8th ed.). Addison Wesley.

Related Assignments