Database Consept ||Fajar Saputra - 21312063||

Name : Fajar Saputra
SIDN : 21312063
Class  : IF 21 B

DATABASE CONSEPT

Database
Database : collection of data that contain information relevant to an enterprise Understanding
Databases from various points of view :

1. A collection of interconnected data groups (archives) that are organized in such a way that they can be reused quickly and easily.
2. A collection of interconnected data stored together in such a way and without unnecessary repetition, to meet various needs.
3. A collection of related files/tables/archives stored in an electronic storage medium.

Basic Database Operation

• Create Database : create a new database
•  Drop Database : Database wipe
• Create Table : create a file/table to a database
•  Drop Table: delete files/tables from a database
• Insert Data: addition/filling
• Retrieve/Search: retrieval by looking for a place to store
• Update: change data from a file/table
• Delete : delete data from a file/table

Database Purpose
The purpose of making a database :

• Speed: speed and ease of use of the database
•  Space: storage space efficiency
• Accuracy : accuracy
• Availability: Availability when needed
• Completeness: completeness of data
• Security : security

System Definition

Consist of

• Functional components (subsystems)
• Cohesive (control) order
• Working together for a purpose System
• An order (integration) consisting of a number of functional components (with specific functional/task units) that are interconnected and jointly aim to fulfill a certain process/job.

DBMS (Database Management System).

A database-management system (DBMS) is a collection of interrelated data and a set of programs to access those data. DBMS provide a way to store and retrieve database information that is both convenient and efficient

Database System Applications

1. Enterprise Information

• Sales: For customer, product, and purchase information Accounting: For payments, receipts, account balances, assets and other accounting information.
• Human resources: For information about employees, salaries, payroll taxes, and benefits, and for generation of paychecks.
•  Manufacturing: For management of the supply chain and for tracking production of items in factories, inventories of items in warehouses and stores, and orders for items.

dd         2. Banking and Finance
      - Banking: For customer information, accounts, loans, and banking transactions

            - Credit card transactions: For purchases on credit cards and generation of monthly statements

            - Finance: For storing information about holdings, sales, and purchases of financial instruments                 such as stocks and bonds; also for storing real-time market data to enable online trading by                     customers and automated trading by the firm.

 

   Database System vs File Processing System

1.                                  Data redundancy & inconsistency

·         different programmers create the files and application programs over a long

·         the various files are likely to have different structures and the programs mayin several programming languages.

·         Moreover, the same information may be duplicated in several places (files).

                 Difficulty in accessing data

·         conventional file-processing environments do not allow needed data to be retrieved in a convenient and efficient manner.

Data isolation

·         data are scattered in various files, and files may be in different formats, writing new application programs to retrieve the appropriate data is difficult

4.                                      Integrity problems

·         The data values stored in the database must satisfy certain types of consistency constraints

·         Contraint in data type, reference, etc.

5.                                      Atomicity problems

·         A computer system, like any other device, is subject to failure.

·         In many applications, it is crucial that, if a failure occurs, the data be restored to the consistent state that existed prior to the failure.

6.                                     Concurrent-access anomalies

·         For the sake of overall performance of the system and faster response, many systems allow multiple users to update the data simultaneously.

7.                                      Security problems

·         Not every user of the database system should be able to access all the data.

Relational Database

A relational database is a type of database that stores and provides access to data points that are related to one another. Relational databases are based on the relational model, an intuitive, straightforward way of representing data in tables. In a relational database, each row in the table is a record with a unique ID called the key. The columns of the table hold attributes of the data, and each record usually has a value for each attribute, making it easy to establish the relationships among data points.

·         Uses a collection of tables to represent both data and the relationships among those data.

·         Each table has multiple columns, and each column has a unique name

·         Tables are also known as relations

·         Record-based models are so named because the database is structured in

·         fixed-format records of several types.

·         Each table contains records of a particular type. Each record type defines a fixed number of fields, or attributes.

·         The columns of the table correspond to the attributes of the record type.

·         The relational data model is the most widely used data model, and a vast majority of current database systems are based on the relational model

 

Structure of Relational Databases

ex strucrure reational db

• Each table is assigned a unique name
• Each table has columns to represent the attribute of the data
• Each table has records of data
• Each record has value for every columns
• A row/records in a table represents relationship among a set of values

• Instructor relation
  ID : primary key
  name : instructor name
  dept_name : Name of the dept.
  salary : instructor salary

Attribute Types

• The set of allowed values for each attribute is called the domainof the attribute
• Attribute values are (normally) required to be atomic; that is, indivisible
• The special valuenull is a member of every domain
• The null value causes complications in the definition of many operations

Database Schema

Database schema : logical design of the database

• Eg. department relation
• department (dept_name, building,budget)

Database instance : snapshot of the data in the database at a given  instant in time

• Eg. the right side table

Keys

• must have a way to specify how tuples within a given relation are distinguished
• expressed in terms of their attributes
• the values of the attribute values of a tuple must be such that they can uniquely identify the tuple
• no two tuples in a relation are allowed to have exactly the same value for all attributes

Superkey

• Superkey : a set of one or more attributes that, taken collectively, allow us to identify uniquely a tuple in the relation
• Eg. the ID attribute of the relation instructor is sufficient to distinguish one instructor tuple from another. Thus, ID is a superkey. 
• The name attribute of instructor, on the other hand, is not a superkey, because several instructors might have the same name.
• Formally, let R denote the set of attributes in the schema of relation r. 
• If we say that a subset K of R is a superkey for r
• we are restricting consideration to instances of relations r in which no two distinct tuples have the same values on all attributes in K. 
• That is, if t1 and t2 are in r and t1 != t2, then t1.K != t2.K.

Primary Key

• must be chosen with care
• the name of a person is obviously not sufficient, because there may be many people with the same name 
• In the United States, the social-security number attribute of a person would be a candidate key.
• Since non-U.S. residents usually do not have social-security numbers, international enterprises must generate their own unique identifiers
• An alternative is to use some unique combination of other attributes as a key
• The primary key should be chosen such that its attribute values are never, or very rarely, changed. 
• Eg. the address field of a person should not be  part of the primary key, since it is likely to change. 
• Social-security numbers, on the other hand, are guaranteed never to change. 
• Unique identifiers generated by enterprises generally do not change, except if two enterprises merge; 
• in such a case the same identifier may have been issued by both enterprises, and are allocation of identifiers may be required to make sure they are unique.

Foreign Key

• A relation, say r1, may include among its attributes the primary key of another relation, say r2. 
• Called a foreign key from r1, referencing r2
• The relation r1 is also called the referencing relation of the foreign key dependency
• r2 is called the referenced relation of the foreign key
• Referential integrity constraints
• the values appearing in specified attributes of any tuple in the referencing relation also appear in specified attributes of at least one tuple in the referenced relation.

example :

dept_name field in left table but in right table deptname foreign key.