Disclaims:
Always study and research the information here first before use! UAYOR!
Showing posts with label
Understanding Oracle SQL and PL/SQL.
Show all posts
Showing posts with label
Understanding Oracle SQL and PL/SQL.
Show all posts
- There cannot be other paths into the database that subvert data integrity; in other words, you can't get in the 'back door' and change the data in such a manner ad data integrity is violated
- The DBMS must prevent data from being modified by machine language intervention
- Data in a relational database can be stored centrally or distributed
- Data from tables on different servers (distributed queries) and from other relational database (heterogeneous queries) can be join by user
- Data integrity must be maintained regardless of the number of copies of data and where it resides
- In order to be considered relational, data integrity must be an internal function of the DBMS; not the application program
- Data integrity means the consistency and accuracy of the data in the database (i.e., keeping the garbage out of the database)
- Data integrity got 3 type:
- entity,
- domain, and
- referential
- Within the database, data integrity can be enforced procedurally or declaratively
- Declarative data integrity involves placing or 'declaring' constraints on column
- Procedural data integrity is maintained through code (i.e., through stored procedures or triggers)
- Logical independence means the relationships among tables can change without impairing the function of applications and adhoc queries
- The database schema or structure of tables and relationships (logical) can change without having to recreate the database or the applications that use it.
- Applications that access data in a relational database must be unaffected by changes in the way the data is physically stored, i.e., the physical structure
- Below example:
- The code in an application that accesses data in a file-based database typically depends on the file format,
- e.g., the code references a 'phone number' field that is 1o characters wide, is preceded by the 'zip code' field, followed by the 'fax number' field...
- If the layout of the data in the file is changed, the application must also be changed.
- In contrast, the storage and access methods (physical) used in a relational database can change without affecting the user or application's ability to work with the data.
- The user still only sees tables (logical structure).
- An application that accesses data in a relational database contains only a basic definition of the data (data type and length); it does not need to know how the data is physically stored or accessed
- Rows are treated as sets for data manipulation operations (SELECT, INSERT, UPDATE, DELETE)
- A relational database must support:
- basic relational algebra operations, such as selection, projection and join
- set operations, such as union, intersection, division and difference
- Views are virtual tables or abstractions of the source tables
- A view is an alternative way of looking at data from one or more tables
- A view definition does not duplicate data
- A view is not a copy of the data in the source tables
- Once created, a view can be manipulated in the same way as a source data
- If you change data in a view, you are changing the underlying data in the source table (although there are limits on how data can be modified from a view)
- There must be a single language that handles all communication with the database management system
- The language must support relational operations with respect to:
- data modification, i.e., SELECT, INSERT, UPDATE, DELETE
- data definition, i.e., CREATE, ALTER, DROP
- administration, i.e., GRANT, REVOKE, DENY, BACKUP, RESTORE
- Structured Query Language (SQL) is the in practice standard for a relational database language
- SQL is a 'non-procedural' or 'declarative' language; it allows users to express what they want from the RDBSMS without specifying the details about where it's located or how to get it
- In addition to user data, a relational database contains data about itself
- There are two types of tables in a RDBMS:
- user tables that contain the 'working' data
- system tables that contain data about the database structure
- System tables can be accessed in the same manner as user tables
- Null must always be interpreted as an unknown value
- 'Unknown' is not the same thing as an empty string ("") or zero
- Comparing a null to any value, including itself, returns NULL
- A relational database does not reference data by physical location; there is not such thing as the 'fifth row in the customers table'
- Each piece of data must be logically accessible by referencing:
- a tables;
- a primary or unique key value; and
- a column
- A database is form by a set of related tables
- All data is represented as tables only, there is no other way the data can be viewed
- A table is a logical grouping of related data in tabular form
- Table also known as relation or entity
- Tabular form also known as rows and columns
- Each row describes an item (person, place or thing)
- Each row contains information about a single item in the table
- Row also known as record or tuple
- Each column describes a single characteristic about an item
- Column as known as field or attribute
- Data is atomic; there is no more than one value associated with the intersection of a row and column
- The relationships among tables are logical; there are no physical relationships among tables
- Data is Presented in Tables
- Data is logically accessible
- Nulls are treated uniformly as Unknown
- Database is self describing
- A simple language is used to communicate with DBMS
- Provides alternatives for viewing
- Support set based or relational operations
- Physical Data Independence
- Logical Data Independence
- Data Integrity is a Function of the DBMS
- Support Distributed Operation
- Data Integrity cannot subverted
- Logical grouping of related data to maintain data in tables
- It is in the form of rows and columns
- Relationships are maintained between tables
- Removed data redundancy
- Faster search for a particular information
A.C.I.D
- Atomicity
- - Either all committed or all rolled back
- Consistency
- - Follows user defined integrity constraints
- Isolation
- - A transaction is invisible to other users until completed
- Durability
- - Once committed the results are permanent and survive future system and crashes
- Finding your data fast
- - Data should be accessible fast enough to cater to the huge number of request every second
- Very similar to Hierarchical Database
- Only differ is Network database support many-to-many relationship: children can have multiple parents and parents can have multiple children
- This type of database is complicated and difficult to maintain
- Main user is computer programmers rather than real end users to solve real time problems
- Data is organised in tree structure as parent and child
- Support one-to-many relationship - parent can have multiple children but a child can have only one parent
- Because of one-to-many relationship, data is systematically accessible. Parent have pointers to their children
- To get to a low-level table start from the root and work all the way down
- Many-to-many relationship is not supported
- Windows based directory management system is one of Hierarchical database.
- A flat file is where data is stored
- To search for a particular data, the whole file must be read and written back.
- Any data must be appended at the end of the file
- Simultaneous transactions or sudden power shutdown etc can render the data corrupted
- If 2 users access at the same time, either
- - Both inserts are successful
- - One of the inserts is lost
- - Info from both inserts is mixed and data corrupted resulting in the file getting corrupted.