"Logical and Physical Design" Please respond to the following:
In order to ensure optimal database performance, the logical and physical design should consider the user requirements thoroughly. Suppose you have been hired to transform a conceptual model into a logical model for a sales database.
Describe the specific steps that you must perform in order to appropriately construct the database model.
For each step mentioned, speculate the risks that would take place and how you would avoid or mitigate those risks.
Suggest at least three activities that are required in the physical design process of a database to ensure adequate physical storage and data access.
Analyze why user, security groups, and role definitions are essential to maintain the integrity of the database.
Classmate:
Describe the specific steps that you must perform in order to appropriately construct the database model. For each step mentioned, speculate the risks that would take place and how you would avoid or mitigate those risks.
The conceptual design is independent of all physical considerations, a process that uses data modeling techniques to create a model of a database structure that represents real-world objects, While Logical design maps the conceptual design (ERD) to a specific database model.
The main objective in developing a logical data model for relational database systems is to create an accurate representation of the data, their relationships, and constraints. To achieve this objective, we must identify a suitable set of tables.
The first part of the logical design process is transformation. We will do this part in four steps:
1. Translate from ERD to tables
2. Represent relationships
3. Transform relationships
4. Create preliminary list of tables, and indicate primary keys, alternate/candidate keys, and foreign keys. The requirement of the end users must be understood in order to properly mapping the relationships and its constraints to be able to avoid the risk of creating anomalies and ambiguous data.
The second part of the logical design process is normalization, each identified table, the designer must ensure that all attributes are fully dependent on the identified primary key and that the tables are in at least third normal form (3NF)
The third step is integrity constraints, the definition of the attribute domains and appropriate constraints require and it must be supported by logical data model, mapped these constraints to the proper relational model constraints and they must be declared mandatory to ensure they maintain entity and referential integrity.
Final step is the user requirement validation, this process validates all logical model definitions against all end-user data, transaction and security requirements to ensure the correctness of the logical model. It's very important to discuss the progress with the end users and the stakeholders, because of the possibility of either remove or add to the requirement, when trying to ensure the design meet the agreed users' requirements.
Suggest at least three activities that are required in the physical design process of a database to ensure adequate physical storage and data access. Analyze why user, security groups, and role definitions are essential to maintain the integrity of the database.
Physical design, maps the data storage and access characteristics of a database to ensure its integrity, security and performance. Some of the activities that are required include;
The first is Organization data storage, before defining data storage the volume of data to be managed and the usage patterns, the traffic, data location, indexes to be used and the views type.
Secondly, the integrity and security measures must be defined, for properly authenticate the users by define user and security groups and roles and assign security controls. These will give limited access to the users on what they can do, go and manage in the database so that users from sales department do not interfere with finance department data.
Lastly, Performance measures must also be determined, which can be affected by characteristics of the storage media, like seek time, sector and block size, buffer pool size and data access speed and efficiency.