A Comprehensive Guide to Database Design and Management

Types of databases

• Types of databases

• Why are databases valuable assets for decision making

• Importance of database design

• How modern databases have evolved from file systems

• Flaws in file system data management

• Main components of the database system

• Main functions of a DBMS

• Data modeling

• Basic data-modeling building blocks

• Writing business rules

• Database design

• How data models have evolved

• Emerging alternative data models

• Classification of data models by their level of abstraction

• Relational database model basic component

• Logical view of entities and relations

• Relational database operators

• Data dictionary

• The system catalog

• Data redundancy

• Indexing

• Relational algebra concepts – Unary and Binary

• Entity Relationship Diagrams

• How entities and relationships are defined, refined, and incorporated into the database design process

• How ERD components affect database design and implementation

• Using flexible design to manage conflicting goals

• Characteristics of good primary key• Bottom-up approach to database design

• Role of normalization in the database design process

• Improving database structure

• Normalization process - 1NF, 2NF, 3NF, BCNF, and 4NF

• Using normalization and ER modeling concurrently to produce a good database design

• Some situations which require denormalization

• The basic commands and functions of SQL

• DDL – How to use SQL for data administration (to create tables and indexes)

• DML – How to use SQL for data manipulation (to add, modify, delete, and retrieve data)

• Queries – How to use SQL to query a database for useful information

• CRUD (Create, Retrieve, Update, Delete) operations

• Set operators UNION, UNION ALL, INTERSECT, MINUS

• Virtual tables/ Views,

• Virtual Columns

Understanding stored procedures –
• PL/SQL,

• Functions,

• Scalar data types,

• Anchored data types.

• Exception Handling.