Question

1.Discuss the interdependence that exists between DSDLC stages.

2.How does normalization eradicate update anomalies from a relation?

3.The scope of database security extends beyond just DBMS controls. Discuss the role of the database administrator in database security and recovery.

Answer 1

1. There are many phases that are related to one another while building an information system. Starting from Planning, Requirement analysis, designing database, choosing database, design of Application, making prototypes, testing them, launch and maintenance etc are some phases in the lifecycle of an information system, (DSDLC), to name a few of them. These are all inter-connected and the output of one stage surely affects the other stages.

For instance, a recognized laboratory needs to build a database for the Covid samples they are testing. As a database designer, you need to be clear about what exactly the client (lab) needs from its new application - whether its stats based on date, or region, or gender, or type of illness, or may be the total positive cases in a day/month etc. Be it the design of the database, or application, or testing strategy ....all depends on them. Hence, identifying the mission statement, its objectives and requirements are of utmost importance, as these will affect the entire lifecycle of the system.  Otherwise, you will end up building an unreliable system with poor performance.

2.   An Update anomaly occurs when one entity changes and you need to make this modification in all places where this entity gets repeated. If many duplicate records exist and an update has to be done, you may miss few of them and inconsistency occurs in the database.

Normalization makes it possible for relations to achieve the properties of minimal redundancy and minimal anomalies. We need to decompose a relation into smaller relations , if they dont meet the necessary requirements. They make way for efficient relation schemas using keys and relationships, in the process of normalization.

1NF states that domain of any attribute should contain only single values. For instance, a Department table consisting of multiple locations in DLOC field makes it difficult to make an update for a single location - you might have to probably cram the entire list of locations in each record in your table and search for the place. 2NF does not allow Partial Dependencies. For instance, consider an Employee table which stores eid, pnum,hours, ename.

{eid, pnum} -> {hours, ename}, which is the key here. But {eid} -> {ename}, where ename is a nonkey attribute. Hence we decompose this like, {eid} -> {ename}, {eid, pnum} -> {hours}. This makes it easy to make changes in say, ename field  - only in one place. Similar examples come up for 3NF as well.

3. Database Security is the most essential aspect to any company. Identifying the Assets, their threats and mechanisms to protect them, are crucial steps in implementing a secure database system. You also need a model of security to build on security aspects - detailing the concepts, policies, access control, authorization, authentication, auditing etc.

A Database Administartor does the security management. Makes sure that data is available and only authorized users access the database. Coordinates and maintains the use of DB by acquiring software and hardware as needed. Also, looks into system response related issues. Creates a backup and recovery plans/steps to use - whenever a system failure happens, make sure that they are followed, done as per schedule. The recovery procedures may be different for different kinds of failures. A DBA has to prepare for all such kind of situations.