Concepts And Database Design Week 7 Assignment

Concepts And Database Designweek 7 Assignment Concepts And Database

Create a Data Dictionary with Description, Data Type, Format, and Primary/Foreign Keys. The data dictionary provides a thorough description of the data objects such as table names, attributes, contents, data types, formats, range, requirements, primary keys, and foreign keys, as well as references to related tables. Building a data dictionary ensures consistency in terminology and naming conventions across database maintenance and upgrades.

Construct SQL queries to:

1. Show days between the first and last invoice per month per employee, utilizing self-joins and Oracle functions to compare invoice dates within the same month.

2. Display the expected payment date if invoiced due in 30 days, referencing customer IDs in the customer table.

3. List all distinct area codes from customers efficiently using the SQL DISTINCT clause to eliminate duplicates.

Further, outline the necessary activities to implement a valid database design process, including the four stages: conceptual, logical, physical, and DBMS selection. The conceptual design involves identifying entities, attributes, relationships, and constraints independent of specific database software. Data analysis, requirements gathering, and normalization are critical early steps. Developing the Entity-Relationship (ER) model includes validating the design against user requirements and business rules, and possibly distributing the database for optimization across multiple locations.

The data dictionary example given contains table names, attribute names, contents, types, formats, range, required status, and primary/foreign key references, exemplifying the level of detail required in comprehensive documentation.

References:

  1. Coronel, C., Morris, S., & Rob, P. (2013). Database Systems (10th ed.). Cengage Learning.
  2. Turban, E., & Volonino, L. (2011). Information Technology for Management (8th ed.). Wiley.
  3. Batini, C., Ceri, S., & Navathe, S. B. (1992). Conceptual database design: An Entity-relationship approach. Benjamin/Cummings Publishing.
  4. Elmasri, R., & Navathe, S. B. (2015). Fundamentals of Database Systems. Pearson.
  5. Hoffer, J. A., Venkataraman, R., & Topi, H. (2016). Modern Database Management. Pearson.
  6. Date, C. J. (2003). An Introduction to Database Systems. Addison Wesley.
  7. Rob, P., & Coronel, C. (2007). Database Systems: Design, Implementation, & Management. Cengage Learning.
  8. Coronel, C., & Rob, P. (2012). Database Systems. Cengage.
  9. Kroenke, D. M., & Kroenke, V. (2013). Database Concepts. Pearson.
  10. Connolly, T., & Begg, C. (2014). Database Systems: A Practical Approach to Design, Implementation, and Management. Pearson.

Related Assignments