MODERN DATABASE DEVELOPMENT

For Phase III of the Course Project, you will transform your Logical and Relational Design into a Physical Design based on the Relational Model. You will follow the physical design process covered in Chapter 8 in the textbook. For this Physical Design & Performance phase (and also for the Implementation phase), you will be using both MySQL and MS SQL Server as two commercial relational DBMSs. You will list the physical design issues/questions you think you need to address. You will discuss and fully document in as much detail as necessary - the issues and their solutions and why you think your design might have the issue listed and why you think your solution will adequately address and resolve those issues. Specifically, you will address the following points: Regulatory Compliance: Does your organization need to comply with state, federal, international rules and regulations related to the data you will be storing in your proposed (and chosen) database? If so, how will you be implementing the regulatory compliance part of your physical design? You need to give all specifics. Data Volumes and Usage Analysis: You need to come up with some estimates for the data volume and usage analysis. You need to explain and justify why and how you came up with those estimates. You need to use the proper notation to add the data volume and usage analysis figures to your ER (or EER) diagram and included the updated ER (or EER) diagram in the document you will be submitting for Phase III. Designing Fields: You need to design fields and choose proper datatypes, meaningful and legal names for fields. Coding Techniques: You need to decide if there is a need for coding techniques and lookup tables. Controlling Data Integrity: You need to determine if there is a need for default values for some fields, range control, Null or Not Null constraint, other constraints, and referential integrity. You need to determine which fields will play the role of primary keys and foreign keys. Handling Missing Data: You need to decide how to handle missing data if and when applicable. Denormalizing and Partitioning Data: You need to decide if there is a need to denormalize and/or partition some tables to improve performance. Follow the guidelines for denormalization and partitioning on pages 213 through 219 in the textbook. Designing Physical Database Files: You need to decide what type of file organization(s) will best serve and support the needs of your database operations and if you need to use a mix of different file organizations. o Clustering Files: You need to decide if there is a need to cluster files. o Designing Controls for Files: You need to decide on how to protect the database files from destruction or contamination or how to reconstruct the files if they are damaged. You need to determine if and when and where to create indexes and what type of indexes to create and why. You need to decide if and how to design your database for optimal query performance and why you need to do this. Important Notes: You must address all the above issues twice and separately: o Once for MySQL as your commercial RDMBS and o Once for MS SQL Server as your commercial RDMBS Your submitted PDF document must have two different and separate sections: one for MySQL and one for MS SQL Server. You must fully document and justify all your physical design decisions (why, how, what, when, etc.) and their solutions. o Some of the design decisions and their solutions might depend on the specific commercial RDMBS you will be using.