Lost your password? Please enter your email address. You will receive a link and will create a new password via email.
Please briefly explain why you feel this question should be reported.
Please briefly explain why you feel this answer should be reported.
Please briefly explain why you feel this user should be reported.
what is non first normal form?
The First Normal Form (1NF) is the fundamental requirement for the relational model and states that values of attributes have to be atomic. Simultaneously, 1NF is responsible for several drawbacks of the relational model ➔ an extended data model should not require 1NF. Objects of the Non First NormaRead more
The First Normal Form (1NF) is the fundamental requirement for the relational model and states that values of attributes have to be atomic.
Simultaneously, 1NF is responsible for several drawbacks of the relational model
➔ an extended data model should not require 1NF.
Objects of the Non First Normal Form are defined as follows:
• Every atomic value (such as integer, float, string) is an object.
• If a1, a2, … , an are distinct attribute names and o1, o2, … , on are objects, then (a1:o1, a2:o2, … , an:on) is a tuple object.
• If o1, o2, … , on are objects, then {o1, o2, … , on} is a set object.
Each tuple of a NF2 relation can contain such objects
See lessDiscuss why the weaknesses of the relational data model and relational DBMS may make them unsuitable for advanced database applications?
1. Poor representation of real-world entities -- The process of deriving relations from a conceptual database often leads to the creation of relations that do not directly correspond to modeled entity types. Cause • complex entities consists of other entities • entities are described by multi-valuedRead more
1. Poor representation of real-world entities —
The process of deriving relations from a conceptual database often leads to the creation of relations that do not directly correspond to modeled entity types.
Cause
• complex entities consists of other entities
• entities are described by multi-valued attributes
Result
• fragmentation of a entity type into many relations
• is inefficient and leads to many joins during query processing
2. Semantic Overloading–
The relational model uses only one construct for representing data and relationships between data: the relation.
There is no mechanism to distinguish between entities and relationships, or to distinguish between different kinds of relationship that exist between entities.
Often, attributes of different entities are mixed within one relation. Considering only the given relation, we cannot know, which attributes belong to a certain entity or relationship.
➔ the relational model is said to be semantically overloaded
3. Poor support for integrity and general constraints–
Integrity refers to the validity and consistency of stored data.
Integrity is usually expressed in terms of constraints, which are consistency rules that the database is not permitted to violate.
Many commercial systems do not fully support these constraints and it is necessary to build them into the applications. This is dangerous and can lead to duplication of effort and inconsistencies.
There is no support for general constraints in the relational model, which again means that they have to be built into the DBMS or the application.
4. Homogeneous data structure —
The relational model assumes both horizontal and vertical homogeneity.
• Horizontal homogeneity means that each tuple of a relation must be composed of the same attributes
• Vertical homogeneity means that the values in a particular column of a relation must all come from the same domain
The first normal form requires that the intersection of a row and column must be an atomic value.
Drawbacks of Homogeneity
• too restrictive for many (more complex) “real-world” objects
• leads to unnatural and inefficient joins
5. Limited Operations–
The relational model has only a fixed set of operations, such as set and tuple-oriented operations, operations that are provided in the SQL specification.
Since (at least older versions of) SQL is not computationally complete, new operations cannot be specified.
This is too restrictive to model the behavior of many real-world objects.
6. Difficulty handling recursive queries–
Atomicity of data means that repeating groups are not allowed in the relational model.
As a result, it is extremely difficult to handle recursive queries, that is, queries about relationships that a relation has with itself (directly or indirectly).
7. Impedance Mismatch Older versions of SQL lack computational completeness and could not be used to develop more complex database applications.
To overcome this problem, SQL can be embedded in a procedural programming language.
However, this approach produces an impedance mismatch, because different programming paradigms are mixed:
• SQL is a declarative language that handles many rows of data at a time
• common high-level languages (such as C) are procedural languages that can handle only one row of data at a time Since the type systems are different, type checking is prevented and necessary conversions require a lot of programming effort and runtime resources.
See lessDiscuss why the weaknesses of the relational data model and relational DBMS may make them unsuitable for advanced database applications?
Poor representation of real-world entities - The process of deriving relations from a conceptual database often leads to the creation of relations that do not directly correspond to modeled entity types. Cause • complex entities consists of other entities • entities are described by multi-valued attRead more
The process of deriving relations from a conceptual database often leads to the creation of relations that do not directly correspond to modeled entity types. Cause • complex entities consists of other entities • entities are described by multi-valued attributes Result • fragmentation of a entity type into many relations • is inefficient and leads to many joins during query processing 2. Semantic Overloading The relational model uses only one construct for representing data and relationships between data: the relation. There is no mechanism to distinguish between entities and relationships, or to distinguish between different kinds of relationship that exist between entities. Often, attributes of different entities are mixed within one relation. Considering only the given relation, we cannot know, which attributes belong to a certain entity or relationship. ➔ the relational model is said to be semantically overloaded 3. Poor support for integrity and general constraints Integrity refers to the validity and consistency of stored data. Integrity is usually expressed in terms of constraints, which are consistency rules that the database is not permitted to violate. Many commercial systems do not fully support these constraints and it is necessary to build them into the applications. This is dangerous and can lead to duplication of effort and inconsistencies. There is no support for general constraints in the relational model, which again means that they have to be built into the DBMS or the application. 4. Homogeneous data structure The relational model assumes both horizontal and vertical homogeneity. • Horizontal homogeneity means that each tuple of a relation must be composed of the same attributes • Vertical homogeneity means that the values in a particular column of a relation must all come from the same domain The first normal form requires that the intersection of a row and column must be an atomic value. Drawbacks of Homogeneity • too restrictive for many (more complex) “real-world” objects • leads to unnatural and inefficient joins. 5. Limited Operations The relational model has only a fixed set of operations, such as set and tuple-oriented operations, operations that are provided in the SQL specification. Since (at least older versions of) SQL is not computationally complete, new operations cannot be specified. This is too restrictive to model the behavior of many real-world objects. 6. Difficulty handling recursive queries Atomicity of data means that repeating groups are not allowed in the relational model. As a result, it is extremely difficult to handle recursive queries, that is, queries about relationships that a relation has with itself (directly or indirectly). 7. Impedance Mismatch Older versions of SQL lack computational completeness and could not be used to develop more complex database applications. To overcome this problem, SQL can be embedded in a procedural programming language. However, this approach produces an impedance mismatch, because different programming paradigms are mixed: • SQL is a declarative language that handles many rows of data at a time • common high-level languages (such as C) are procedural languages that can handle only one row of data at a time Since the type systems are different, type checking is prevented and necessary conversions require a lot of programming effort and runtime resources.
See lessDiscuss the general characteristics of advanced database applications
large number of data types Less portable example: CAD, CAM, OIS Real-time access and large number of users, For example: CAD, Network Management System Large volume of data, For example: GIS, CAM
large number of data types
Less portable example: CAD, CAM, OIS
Real-time access and large number of users, For example: CAD, Network Management System
Large volume of data, For example: GIS, CAM
See lessAdvantage and Disadvantage of Trigger
Advantages: Elimination of redundant code Simplifying modifications Increased security Improved integrity Improved processing power Good fit with the client-server architecture Disadvantage: Performance Overhead Complexity Hidden functionality Cascading effects Cannot be scheduled
Advantages:
Disadvantage:
what are Triggers?
A trigger is an SQL statement that is executed automatically by the DBMS as a side effect of a modification - the triggering event - to a named table. The act of executing a trigger is sometimes known as firing the trigger. Some Use Cases : • validating input data and maintaining complex integrity conRead more
A trigger is an SQL statement that is executed automatically by the DBMS as a side effect of a modification – the triggering event – to a named table.
The act of executing a trigger is sometimes known as firing the trigger.
Some Use Cases :
• validating input data and maintaining complex integrity constraints.
• supporting alerts that action needs to be taken when a table is updated in some way.
• refreshing derived attributes after an update operation.
• maintaining audit information, by recording the changes made, and by whom.
• supporting replication.
The basic format of the CREATE TRIGGER statement is as follows:
CREATE TRIGGER TriggerName
BEFORE | AFTER | INSTEAD OF <triggerEvent> ON <TableName>
[REFERENCING <oldOrNewValuesAliasList>]
[FOR EACH {ROW | STATEMENT}]
[WHEN (triggerCondition)]
<triggerBody>
• Triggering events include insertion, deletion, and update of rows in a table. In the latter case only, a triggering event can also be set to cover specific named columns of a table.
• A trigger has an associated timing of either BEFORE, AFTER, or INSTEAD OF.
• A BEFORE trigger is fired before the associated event occurs
• An AFTER trigger is fired after the associated event occurs
• An INSTEAD OF trigger is fired in place of the trigger event.
Three different types of Triggers
Row-Level Triggers (FOR EACH ROW) • execute for each row of the table that is affected by the triggering event
See lessStatement-Level Triggers (FOR EACH STATEMENT) • execute only once even if multiple rows are affected by the triggering event.
Cascading Triggers .Triggers can also activate themselves one after the other.
This can happen when the trigger action makes a change to the database that has the effect of causing another event that has a trigger associated with it to fire.
How is a procedure different from a function?
Both can modify and return data passed to them as a parameter, but a function can only return a single value to the caller.
Both can modify and return data passed to them as a parameter, but a function can only return a single value to the caller.
See lessWhat are cursors, exceptions, stored procedures
Cursor- A SELECT statement can be used if the query returns exactly one row. To handle a query that can return an arbitrary number of rows, PL/SQL uses cursors to allow the rows of a query result to be accessed one at a time. The cursor can be advanced by one to access the next row. A cursor must beRead more
Cursor- A SELECT statement can be used if the query returns exactly one row. To handle a query that can return an arbitrary number of rows, PL/SQL uses cursors to allow the rows of a query result to be accessed one at a time. The cursor can be advanced by one to access the next row.
A cursor must be
• declared and opened before it can be used
• closed to deactivate it after it is no longer required
Once the cursor has been opened, the rows of the query result can be retrieved one at a time using a FETCH statement.
Exceptions:
An exception is an identifier in PL/SQL raised during the execution of a block that terminates its main body of actions, although some final actions can be performed.
An exception can be raised automatically or explicitly using the RAISE statement
To handle raised exceptions, separate routines called exception handlers are specified.
The exception handler itself is defined at the end of the PL/SQL block.
Stored Procedures:
Sub program in PL/SQL that can take parameter and be invoked. They can modify and return data passed to them as a parameter.
A stored procedure is a subroutine available to applications that access a relational database management system (RDBMS). Such procedures are stored in the database data dictionary.
Typical uses for stored procedures include data-validation (integrated into the database) or access-control mechanisms.
See lessWhat led to the introduction of PL/SQL and SQL/PSM?
Initial versions of SQL have been computationally incomplete (without programming constructs). Later versions of SQL could be embedded in a high-level programming language, but produced an impedance mismatch, because of the mixing of different programming paradigms. SQL/PSM: SQL has been extendedRead more
Initial versions of SQL have been computationally incomplete (without programming constructs). Later versions of SQL could be embedded in a high-level programming language, but produced an impedance mismatch, because of the mixing of different programming paradigms.
SQL/PSM:
SQL has been extended to a full programming language
The extensions are known as SQL/PSM (Persistent Stored Modules)
PL/SQL: PL/SQL (Procedural Language SQL) is the Oracle version of an SQL programming language and has concepts similar to modern programming languages, such as variable and constant declarations, control structures, exception handling, and modularization.
See lessConcepts of the (Extended) Entity Relationship Model
The Extended Entity-Relationship Model is a more complex and high-level model that extends an E-R diagram to include more types of abstraction, and to more clearly express constraints. All of the concepts contained within an E-R diagram are included in the E-R model, along with additional concepts tRead more
The Extended Entity-Relationship Model is a more complex and high-level model that extends an E-R diagram to include more types of abstraction, and to more clearly express constraints. All of the concepts contained within an E-R diagram are included in the E-R model, along with additional concepts that cover more semantic information.
These additional concepts include:- generalization/specialization, union, inheritance, and subclass/super class.
Relation: A relation is a table with columns and rows
Attribute: An attribute is a named column of a relation.
Domain: A domain is the set of allowable values for one or more attributes.
Primary key: the candidate key that is selected to identify tuples uniquely within the relation.
Foreign key: an attribute, or set of attributes, within one relation that matches the candidate key of some (possibly the same) relation.
Integrity constraints:
Domain integrity: Describes the restriction on the set of allowed values for an attribute.
Entity Integrity: No attribute of a primary key can be null.
Referential Integrity: If a foreign key exists in a relation, either the foreign key value must match a candidate key value of some tuple in its home relation or the foreign key value must be wholly null.
Referential integrity is a relational database concept, which states that table relationships must always be consistent. In other words, any foreign key field must agree with the primary key that is referenced by the foreign key.
Views: The dynamic result of one or more relational operations operating on the base relations to produce another relation. A view is a virtual relation that does not necessarily exist in the Database but can be produced upon request by a particular user, at the time of request.
Null represents a value for an attribute that is currently unknown or is not applicable for this tuple.
See less