Topic 3 AIS

What is primary key?

- A primary key is the attribute or combination of attributes (composite/concatenated key), that uniquely identifies a specific row in a table. -Every dataset must have a unique identifier of records within the dataset

What is data dictionary?

- Contains information about the structure of the database. - The data elements composing the database are fully described in a data dictionary, which serves as a repository containing facts about the structure of the data elements employed in applications

What is file-oriented approach?

- Duplication of data (files) approach applications poses a data maintenance problem and can potentially cause problems of data inconsistencies - The task of maintaining files can become very complex as the number of applications multiplies, since each application creates its own set of files - Inability to share data in existing files increases the time required to create new applications, since all files needed for the new application must be independently created - Data structures and the procedures that modify the data are both defined within the same program. Data structures and/or procedures cannot be independently modified.

What are some notes on data base approach?

- Everyone can access - One single database system - Very expensive - Security issue - Pay to make employees learn

What is data base approach?

- Involve creating an organization wide repository (database) of data that all applications and all application programs and hence users can share. - Database is a systematic collection of data. - Since the data in a database is organized, it makes data management easy. - Database management system (DBMS) -A collection of programs which enables its users to access database, manipulate data, and help in representation of data -Help control access to the database by various users

What are some examples of file oriented approach?

- Limitation of system - Redundancy issue, files data rely on each other - Complicated

What are the three types of attributes?

- Primary key - Foreign key - Non key attributes

What are advantages of data base approach?

-Integration is achieved by combining files into larger "pools" of data that many application programs can access. An example is an employee database that consolidates data formerly contained in payroll, personnel, and job skills master files. This makes it easier for information to be combined in unlimited ways. -Integrating data makes it easier to share data with all authorized users. -Reports can be revised easily and generated as needed and the database can be easily browsed to research a problem or obtain detailed information underlying a summary report. -Because data items are usually stored only once, data redundancy and data inconsistencies are minimized. -Because data and the programs that use them are independent of one another, each can be changed without having to change the other. This makes programming easier and simplifies data management. -Data management is more efficient because a database administrator is responsible for coordinating, controlling, and managing the data across the organization -In a database system, relationships, such as the association between selling costs and promotional campaigns, can be explicitly defined and used in the preparation of management reports

What are the disadvantages of data base approach?

-The administration of large-scale database systems requires significant resources and expertise. -Configuring the database to insure data integrity requires considerable expertise and intricate knowledge of DBMS features. -Need to be competent in using the DBMS to access data for the purposes (e.g., generating useful information and fulfilling audit objectives). -Backup, control and security are typically centralized, potentially making the organization vulnerable to a hacker who can break through the central security shield

What are the basic requirements of a relational database?

1. Every column in a row must be single valued. 2. Primary keys cannot be null 3. Foreign keys, if not null, must have values that correspond to the value of a primary key in another table 4. All nonkey attribute in a table should describe a characteristic about the object identified by the primary keys 5. Each attribute must be uniquely named

What is foreign key?

A foreign key is an attribute in a table that is a primary key in another table. Foreign keys are used to link tables.

What is data model?

An abstract visual representation of the contents of a database

What are the three levels of schemas?

Conceptual, External and Internal

What is external level schemas?

Consists of a set of individual user views of portions of the database, each of which is also referred to as subschema

What is data directory?

Contains data about data, or "meta data." It provides a variety of information about the contents of the database.

What is schema?

Describes the logical structure of a database

What is non key attributes?

Every filed in a data set other than the primary key

What is referential integrity rule?

Foreign keys which link rows in one table to rows in another table must have values that correspond to the value of a primary key in another table. The main purpose of referential integrity rule is to ensure the validity of foreign keys

What is transitive functional dependency?

If a non-key attribute depends on another non-key attribute

What is partial functional dependency?

If a non-key attribute depends on part (but not all) of key attributes (primary key)

What is full functional dependency?

If an attribute depends on all of key attributes (primary key)

What is functional dependency?

If there is only one possible value of Y for every value of X, then Y is said to be functionally dependent on X

What is entity integrity rule?

Means that a non-null primary key ensure that every row in a table represents something and that it can be identified. It prevents tables from having duplicate or missing primary keys which would prevent rows from being located and queries from being answered.

What is internal level schema?

Provides a low level view of the database, describes how the database are actually stored

What is relational data model?

Represents everything in the database as being stored in the form of tables

What is conceptual level schemas?

The organization wide view of the entire database

What is data manipulation language (DML)?

Used for data maintenance, which includes such operations as updating, inserting, and deleting portions of the database

What is Data Definition Language (DDL)?

Used to (1) build the data dictionary, (2) initialize or create the database, (3) describe the logical views for each individual user or programmer, and (4) specify any limitations or constraints on security imposed on database records or fields.

What is data query language (DQL)?

Used to interrogate the database. The DQL retrieves, sorts, orders, and presents subsets of the database in response to user queries