- Design: ER
Modeling
- It is a part of information system which is an orchestrated dynamic operations of an organization, it includes collection, storing and managing information necessary for business operations.
- A balance between advancement and obsolescence is crucial,
and a database must have the power of transformations to
accommodate any changes in during business operations.
SQL developer provides some design tool design and create db from scratch.
- Indexing With SQL you may create and index
on a very large table to retrieve rows quickly, using ROWID. As
indexes and tables both are synched, DML operations like INSERT,
UPDATE, DELETE, slow down processes and may corrupt the table. The
primary or Foreign Keys are implicit "indexes", the indexes created
with SQL statements are explicit indexes.
SQL-Views A view is virtual table or a stored query can be created from a single table, multiple tables or from another view.
SQL Script:
SQL > CREATE VIEW e_view AS
SELECT eid, empname, job
FROM emp1;
- Security:
Limiting or regulating users role and privileges, and protecting
database from any data corruption or damages.
- Authorize actions like :Read, Insert, Update, alter or delete
- Manage code module to enforce security on the procedures and impose constraints to reduce the chances of data corruptions.
- Integrity: Check on referential integrity, non-key integrity, matching correct data type, and
- Concurrency :
avoid undesired duplications or redundancies
- Lock: locking table or row to defer other users, when the same is being checked out by another user.
- Versioning :Keeping track of any revision or upgrade of the DB
- Resolve conflicting data
- Backup/Recovery: Backing up the db and keeping transaction log
etc.
- Master Table: Final master table
- Forward log : Updated record
- Backward log :Record in the past
- Transaction : Transactions details like time and the users, affected row etc.
