Database Management Design

Issues to watch out for when merging entities from different ER models:

synonyms homonyms transitive supertype/subtype

Data manipulation

Powerful SQL operations for retrieving and modifying data

Normalization

Primarily a tool to validate and improve a logical design so that it satisfies certain constraints that avoid unnecessary duplication of data The process of decomposing relations with anomalies to produce smaller, well-structured relations

Enterprise key

Primary keys that are unique in the whole database, not just within a single relation Corresponds with the concept of an object ID in object-oriented systems

The insert operation

Provides a list of attribute values for a new tuple t that is to be inserted into a relation R Can violate any of the four types of constraints If an insertion violates one or more constraints Default option is to reject the insertion

What do you mean by Range?

Range of a given attribute in a table specifies the upper and lower boundaries of the values that an attributes may have. Range refers to set of possible values an attribute can have or validly contain. For example: the range for entering a month in a date is from 0-12, range.

Synonyms

two or more attributes with different names but with similar meanings For example, Employee ID and Employee No

what is necessary to have a relation ?

unique rows and no multivalue attributes

Action assertion

Business Rules

How does database integrity differ from database security?

.Data integrity and data security are two different aspects that make sure the usability of data is preserved all the time. Main difference between integrity and security is that integrity deals with the validity of data, while security deals with protection of data. Backing up, designing suitable user interfaces and error detection/correction in data are some of the means to preserve integrity, while authentication/authorization, encryptions and masking are some of the popular means of data security. Suitable control mechanisms can be used for both security and integrity.

integrity constraints

1.domain constraints 2.entity constraints 3.Action assertion 4.Referential integrity

Business rules:

A business rule is statement that imposes some form of constraint on a specific aspect of the database, such as the elements within a field specification for a particular field or the characteristics of a given relationship. You base a business rule on the way the organization perceives and uses its data, which you determine from the manner in which the organization functions or conducts its business For example : A man and a woman who works in same company, cannot have a marriage. So database administrators create that constraint.

Define Database

A database is a collection of information that is organized so that it can easily be accessed, managed, and updated. In one view, databases can be classified according to types of content: bibliographic, full-text, numeric, and images. In computing, databases are sometimes classified according to their organizational approach. The most prevalent approach is the relational database, a tabular database in which data is defined so that it can be reorganized and accessed in a number of different ways. A distributed database is one that can be dispersed or replicated among different points in a network. An object-oriented programming database is one that is congruent with the data defined in object classes and subclasses.

Well-structured data

A relation that contains minimal data redundancy and allows users to insert, delete, and update rows without causing data inconsistencies

What are Advantages and Disadvantages of DBMS?

Advantages: Reduced data redundancy Reduced updating errors and increased consistency Greater data integrity and independence from applications programs Improved data access to users through use of host and query languages Improved data security Reduced data entry, storage, and retrieval costs Facilitated development of new applications program Disadvantages: Database systems are complex, difficult, and time-consuming to design Substantial hardware and software start-up costs Damage to database affects virtually all applications programs Extensive conversion costs in moving form a file-based system to a database system Initial training required for all programmers and users.

Domain Constraints

Allowable values for an attribute (See Table 4-1) Typically include: Numeric data types for integers and real numbers Characters Booleans Fixed-length strings Variable-length strings Date, time, timestamp Money Other special data types

What is an Attribute?

An attribute in a table is a named column or they are the set of important properties which describes the particular entity. An attribute may consist of name, roll number, age etc.

Alternative Keys

An attribute or attributes that uniquely identify an instance of an entity. If more than one attribute or group of attributes uniquely identify an instance of an entity, the alternate keys are those attributes or groups of attributes not selected as the primary key. A unique index for each alternate key is generated.

Why do these anomalies exist?

Because there are multiple themes (entity types) in one relation. This results in duplication and an unnecessary dependency between the entities.

What is Data Dictionary?

Data dictionary is defined as structured repository of data about data.it means that data dictionary does not contain any actual data from the database but it contains set of all the precise definitions of the terms and symbols used in the database.it is a DBMS catalog which contains information like data types, relationships and data constraints of the database.it also convey information who are the authorized users and what are their access rights.

Components of relational model

Data integrity , Data manipulation, Data structure

What is Data?

Data is a plural of datum, which is originally a Latin noun meaning "something given." Today, data is used in English both as a plural noun meaning "facts or pieces of information" and as a singular mass noun meaning "information". In computing, data is information that has been translated into a form that is more convenient to move or process.

Functional dependency

Dealing with Normalization: The value of one attribute (the determinant) determines the value of another attribute.

What are Derived Attributes?

Derived attributes are those attributes which are based on and are derived from the attributes of another table or a relation. The derived attributes may contain new values or the values from the base table from which it was derived. Derived attributes are effectively read-only since there is no place to write them back to. Also, because derived attributes don't directly point to anything in the database, they cannot be used as primary keys. For example: a derived attribute person's full name may be derived from attribute person's first name and the last name or age of a person is derived from his/her birth date.

6 properties of relations

Each relation (or table) in a database has a unique name. 2. An entry at the intersection of each row and column is atomic (or single valued). There can be only one value associated with each attribute on a specific row of a table; no multivalued attributes are allowed in a relation. 3. Each row is unique; no two rows in a relation can be identical. 4. Each attribute (or column) within a table has a unique name. 5. The sequence of columns (left to right) is insignificant. The order of the columns in a relation can be changed without changing the meaning or use of the relation. 6. The sequence of rows (top to bottom) is insignificant. As with columns, the order of the rows of a relation may be changed or stored in any sequence

database integrity

In database, database integrity is accuracy and consistency of the data

Basic operations that change the states of relations in the database:

Insert Delete Update (or Modify)

Semantic integrity constraints

May have to be specified and enforced on a relational database Use triggers and assertions More common to check for these types of constraints within the application programs

Data integrity

Mechanisms for implementing business rules that maintain integrity of manipulated data

What do you mean by Meta data?

Metadata is data about data. An item of metadata may describe an individual data item or a collection of data items. Metadata is used to facilitate the understanding, use and management of data. Metadata defines the nature of the data stored in the database.

The Update Operation

Necessary to specify a condition on attributes of relation Select the tuple (or tuples) to be modified If attribute not part of a primary key nor of a foreign key Usually causes no problems Updating a primary/foreign key Similar issues as with Insert/Delete

entity integrity

No primary key attribute may be null. All primary key fields MUST have data.

Delete Rules

Restrict-don't allow delete of "parent" side if related rows exist in "dependent" side Cascade-automatically delete "dependent" side rows that correspond with the "parent" side row to be deleted Set-to-Null-set the foreign key in the dependent side to null if deleting from the parent side not allowed for weak entities

Data structure

Tables (relations), rows, columns

Foreign key rules

The attributes in FK have the same domain(s) as the primary key attributes PK

What is the Difference between primary key and unique key?

The column holding the primary key constraint cannot accept null values.whereas colum holding the unique constraint can accept null values Each table having only one PRIMARY KEY.And may contain many UNIQUE KEYS

referential integrity

This is the concept of foreign keys. The rule states that the foreign key value can be in two states. The first state is that the foreign key value would refer to a primary key value of another table, or it can be null. Being null could simply mean that there are no relationships, or that the relationship is unknown. Delete rules are apart of this

insertion anomaly

adding new rows forces user to create duplicate data

homonyms

attributes with the same name but different meanings For example, Student ID for Student 1 Student ID for student 2

Modification anomaly

changing data in a row forces changes to other rows because of duplication

Deletion anomaly

deleting rows may cause a loss of data that would be needed for other future rows

transitive

even if relations are in 3NF prior to merging, they may not be after merging

SQL

is designed for managing data in relational database management systems (RDBMS), and originally based upon relational algebra and calculus. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control. SQL was one of the first commercial languages for Edgar F. Codd's relational model.

supertype /subtype

may be hidden prior to merging

5 properties of contrainst

not null ( columns) unique(tables) primary key (tables) foreign key(tables) check (column)