18CSC303J - Database Management System UNIT 2 & 3 - 12 MARKS

 12M:


Phases of database lifecycle along with diagram



Phase 1: Database initial Study



Phase 2: Database Design

  • Most critical DBLC phase

  • Makes sure final product meets requirements

  • Focuses on data requirements

  • Sub-phases

  • Create conceptual design

  • DBMS software selection

  • Create logical design

  • Create physical design


Phase 3: Implementation and Loading

  • Creation of special storage related constructs to house end user tables

  • Data loaded into tables

  • Other issues

  • Performance

  • Security

  • Backup and recovery

  • Integrity

  • Company standards

  • Concurrency controls


Phase 4: Testing and Evaluation

  • Database is tested and fine-tuned for performance, integrity, concurrent access and security constraints

  • Done in parallel with application programming

  • Actions taken if tests fail

  • Fine tuning based on reference manuals

  • Modification of logical design

  • Modification of physical design

  • Upgrade or change DBMS software or hardware


Phase 5: Operation

  • Database considered operational 

  • Starts process of system evaluation

  • Unforeseen problems may surface

  • Demand for change is constant


Phase 6: Maintenance and Evaluation 

  • Types of maintenance

  • Preventative maintenance

  • Corrective maintenance

  • Adaptive maintenance

  • Assignment of access permissions 

  • Periodic security audits

  • Periodic system usage summaries

  • Generation of database access statistics to monitor performance


Extended ER features with diagram

The extended features are

  • Generalization

  • Specialization

  • Aggregation 

These features are used for data abstraction in which abstraction mechanisms are used to hide details of a set of objects


Generalization:

  • It is the process of extracting common properties from a set of entities and create a generalized entity from it

  • It is a bottom up approach

  • For example, STUDENT and FACULTY can be generalized to higher level entity called PERSON

  • In this case, P_NAME and P_ADD become part of PERSON and S_FEE becomes part of STUDENT


Specialization:

  • Specialization is an inversion of generalization

  • In specialization, an entity is divided into sub entities based on their characteristics

  • It is a top down approach 

  • For example, EMPLOYEE entity in an employee management system can be specialized into DEVELOPER, TESTER etc

  • In this case, E_NAME, E_SAL etc become part of EMPLOYEE and TES_TYPE become part of TESTER


Aggregation:

  • An ER diagram is not capable of representing relationship between entity and a relationship which may be required in some scenarios

  • In those cases, a relationship with its corresponding entities is aggregated into a higher level entity

  • For example, EMPLOYEE working for a PROJECT may require some machinery. So REQUIRE relationship is needed between relationship WORKS_FOR and entity MACHINERY

  • In this case, WORKS_FOR relationship with its entities EMPLOYEE and PROJECT is aggregated into single entity


Various join statement with syntax and example

  • A SQL join statement is used to combine data or rows from two or more tables based on a common field between them

  • Different types of joins are:

  • INNER JOIN

  • LEFT JOIN

  • RIGHT JOIN

  • FULL JOIN

  • INNER JOIN:

  • Returns records that have matching values in both tables

  • Selects all rows from both tables as long as the condition is satisfied

  • SELECT STUDENT.NAME, STUDENT.AGE, STUDENTCOURSE.COURSE_ID FROM STUDENT INNER JOIN STUDENTCOURSE ON STUDENT.ROLL_NO = STUDENTCOURSE.ROLL_NO;

            


  • LEFT JOIN:

  • Returns all records from the left table and the matched records from the right table

  • Returns all the rows of the table on the left side of the join and matching rows for the table on the right side of join

  • AKA left outer join

  • SELECT STUDENT.NAME, STUDENTCOURSE.COURSE_ID FROM STUDENT LEFT JOIN STUDENTCOURSE ON STUDENTCOURSE.ROLL_NO = STUDENT.ROLL_NO;

  • RIGHT JOIN:

  • Returns all records from the right table and the matched records from the left table

  • Returns all the rows of the table on the right side of the join and matching rows for the table on the left side of join

  • AKA right outer join

  • SELECT STUDENT.NAME, STUDENTCOURSE.COURSE_ID FROM STUDENT RIGHT JOIN STUDENTCOURSE ON STUDENTCOURSE.ROLL_NO = STUDENT.ROLL_NO;

  • FULL JOIN:

  • Returns all records when there is a match in either left or right table

  • Returns all the rows from both the tables

  • AKA outer join or full outer join

  • SELECT STUDENT.NAME, STUDENTCOURSE.COURSE_ID FROM STUDENT FULL JOIN STUDENTCOURSE ON STUDENTCOURSE.ROLL_NO = STUDENT.ROLL_NO;

           Write sample input and output for the JOIN question

Comments

Post a Comment

Popular posts from this blog

18ECO124T - HUMAN ASSIST DEVICES UNIT 4 & 5 - 12M

Image
12M: SET 1: Write in detail about the pediatric orthosis with a diagram Pediatric orthosis: There are several respects in which a child's orthotic requirements differ from those of an adult and these may extend beyond treatment of the obvious abnormality. Accordingly, before considering specific hardware and research activities, the criteria affecting the performance of an orthosis will be discussed. A child is a particularly dynamic person in terms both of his activity and of his developing personality.  It is important that there is a full appreciation of these factors so that prescription of an orthosis can be made sympathetically and in harmony with his development Human factors: Many physical and emotional factors can affect the success of an orthosis Physical factors can be considered under the headings of deformity, growth and activity Deformity:  Frequently an orthosis is fitted to prevent the development of a deformity, particularly during periods of rapid growth For ...
Read more

18ECO124T - HUMAN ASSIST DEVICES UNIT 4 & 5 - 4M

Image
  4M: SET 1: Discuss in detail about the principles and problems in Intra-Aortic Balloon pump Principles of Intra-Aortic Balloon Pump (IABP) Mechanical Assistance: The IABP is a mechanical device designed to help the heart pump blood; It is inserted into the aorta Counterpulsation: The principle of counterpulsation is central to the IABP's function. The balloon inflates and deflates in sync with the cardiac cycle Inflation and Deflation Timing:    - Diastole (Inflation): During diastole, when the heart is at rest, the balloon inflates    - Systole (Deflation): Just before systole, the balloon deflates Helium Gas: Helium is used to inflate and deflate the balloon Synchronization with ECG: The IABP is synchronized with the patient's electrocardiogram (ECG) to ensure precise timing of inflation and deflation. Problems Associated with Intra-Aortic Balloon Pump (IABP) Vascular Complications Thrombosis and Embolism Balloon Rupture Infection Risk Malposition Mechan...
Read more

18CSE483T - INTELLIGENT MACHINING UNIT 4 & 5 - 12M

Image
12M: Explain in detail the Neural Network model in intelligent machining process. Neural network modeling: Neural networks are simple models of the way the nervous system operates The basic units are neurons, which are typically organized into layers Neural network: A neural network is a simplified model of the way the human brain processes information It works by stimulating a large number of interconnected processing units  The processing units are arranged in layers There are typically three parts in a neural network: An input layer, with units representing the input fields One or more hidden layers An output layer, with a unit or units representing the target fields The units are connected with varying connection strengths or weights Input data are presented to the first layer and values are propagated from each neuron to every neuron in the next layer Eventually, a result is delivered from the output layer The network learns by examining individual records, generating a predic...
Read more