Databases are a collection of logically related data. The data and the relationships included built into databases to fulfill a need of the person or organization in charge of the database. To understand database design, we’ll look at its properties, steps and finally core operations.
The 4 properties of DBMS
1. Atomicity - an operation in a database system should be executed completely or not at all. This means that an operation cannot be interrupted or done partially because it would be completely aborted, and the user would be forced to restart. In other words, it maintains the atomicity of the data.
2. Consistency - all the data in a database system is updated before and after an operation is performed. Every fact in our database needs to be checked to make sure everything is consistent and running smoothly.
3. Isolation - each operation runs individually in a database system, preventing interruptions from each other. The data in one database should not affect the other database. This allows multiple operations and transactions to happen simultaneously without impacting each other.
4. Durability - once an operation is completed successfully within the database system, the results are stored permanently. The final values are committed to the database, where data is protected even after a system failure.
The 4 steps in database design
1. Conceptual model – The first step is visualizing the database using an Entity Relationship model. In this stage we take the business problem and requirements we’re solving for and model it using entities and relationships. An entity is defined as a person, place, object, event, or concept we are storing data on. Entities that share characteristic or properties are then grouped into entity classes. Then the entity classes are associated between them using relationships. These relationships have cardinality constraints, meaning the number of instances one entity can and/of must be related to another one.
The way the ER models are drawn depends on the type of notation that’s used. There are two notations that are most popularly used in DBMS which are: Chen and Crow’s Foot. Below is a side-by-side comparison of the differences between the two.
 |
Source: 2.4. ERD alternatives and variations - A Practical Introduction to Databases. (n.d.). https://runestone.academy/ns/books/published/practical_db/PART2_DATA_MODELING/04-other-notations/other-notations.html
|
2. Logical model – After establishing an ER model, we can move to our logical schema. In our relational model, we’re looking at the data structure, data manipulations and data integrity. Within data structure, we are looking for our keys in our entity classes. A primary key is a unique identifier of a relation, and a foreign key is an attribute within our table that allows a dependent relation to refer to its parent relation. After establishing these components, we’ll be able to manipulate our data using queries.
3. Normalization – A step withing the logical model building that involves data integrity. Data normalization allows the user to check and organize the database to reduce data redundancy in relational tables. It involves getting rid of multivalued attributes, partial dependencies, and transitive dependencies.
4. Choosing a good DBMS – There are many applications that work with database management. It all depends on the business needs and capabilities. Building, storing, and running a database has its cost which is why it’s important to fully understand the normalized relational model before adapting it to a management system.
The 4 core operations of a DBMS (using Oracle SQL)
A database should be able to perform four core operations: creation, retrieval, update, and deletion. We’ll explore these core operations using Oracle SQL commands and queries as examples.
1. Create – part of the Data Definition Language (DDL) SQL commands. In SQL, we’re able to create tables in the database by creating columns, assigning a data type, and adding constraints including primary and foreign keys. In the DDL commands, we’re also able to alter tables by adding, dropping and modifying columns. Below is an example of SQL code for creating a table for products in a company.
CREATE TABLE PUR_PRODUCT(
PRODUCT_ID INTEGER PRIMARY KEY,
PRODUCT_NAME VARCHAR2(100),
STANDARD_COST NUMBER(10,2),
LIST_PRICE NUMBER(10,2)
);
2. Retrieve – part of the Data Manipulation Language (DML) SQL commands. We can retrieve (or select) specific data from our database using the SELECT and FROM functions. This is known as a query. We can filter the data we are looking for using functions like WHERE, GROUP BY, HAVING and ORDER BY. Below is an example using the previously created products table. We’re interested in retrieving all products whose list price is above 100.
SELECT *
FROM PUR_PRODUCT p
WHERE p.LIST_PRICE >= 100;
3. Update – part of DML commands. We can update the data stored in our tables. The ALTER function can be used to update the table itself, but for the data inside we use the UPDATE function. Below is an example, again, using our products table where we update the product name from iPad Air to iPad Air 2.
UPDATE PUR_PRODUCT
SET PRODUCT_NAME='iPad Air 2'
WHERE PRODUCT_ID=600;
4. Delete – we can delete tables using the DROP function from the DDL commands and delete data from a table using the DELETE function from the DML commands. To drop tables, we simply use DROP TABLE followed by the table name. To drop specific records, we have to specify which ones using the WHERE function in the query. Below is an example if we wanted to delete products with a standard cost above 200.
DELETE PUR_PRODUCT
WHERE STANDARD_COST > 200;
Comments
Post a Comment