Database Design Analysis (DDA)

Substance:

  1. Analysis of Relationships Between Tables
  2. Data Type Analysis in Table Dictionary

Case Study 1

  1. Look for gaps without going out of range from the problem limits in chapter 1, such as:
  2. Are the normalization stages in accordance with the normalization concept rules?
  3. Are there still insert, update, and/or delete anomalies?
  4. Is the relational type of the table correct? {one-one} {one-many}{many-many}
  5. Are the data types in the table dictionary correct?
  6. Do you still need additional information collection attributes that you consider important in a particular table?
  7. Show me where the error lies and how your argument should be?

Case Study 2 

Add Entity Relational Diagram. Expand it to include attribute types:

  • Composite
  • Double value
  • Derivative

Use a variety of tools, Microsoft Visio, Dia, Visual Paradigm, or Edraw Max

Based on the case:

  1. Is there any associative entity? If so, please indicate.
  2. Give examples of relationships between entities that are Unary, Binary, and Ternary.

Completion

OPEN / DOWNLOAD FILE

Another Example

OPEN / DOWNLOAD FILE

Case Study Creating a Banking Database

Create a database with the name banking. Then give the command use banking. And work on the table below as an exercise.


Example of a Banking Database Design Task

create table cabang
kdcabang char(5) not null,
nmcabang varchar(15) not null,
alamat varchar(60) not null default null,
primary key (kdcabang))

create table biodata
(idnasabah int not null,
nmnasabah varchar(25) not null,
alamat varchar(60) default null
primary key (idnasabah))

Complete with SQL Server 2000 to create the following tables:

Table Name: Section

| Nama Field | Type    | Width | Description      |
|------------|---------|-------|------------------|
| Kdbag      | Int     | -     | PK, Not Null     |
| Nmbag      | Varchar | 20    | Not Null, Unique |

Table Name: City

| Nama Field | Type    | Width | Description  |
|------------|---------|-------|--------------|
| Kdkota     | Int     | -     | PK, Not Null |
| Nmkota     | Varchar | 15    | Not Null     |

The four tables you created above are master tables, namely to store master data and the data manipulation process does not depend on other tables. Next, below you are asked to create a transaction table whose data input process depends on other tables.

Table Name: Account

| Nama Field | Type | Width | Description  |
|------------|------|-------|--------------|
| Norek      | Int  | -     | PK, Not Null |
| Kdcabang   | Char | 5     | FK           |
| Pin        | Char | (6)   | Not Null     |
| Saldo      | Int  | -     | -            |

Table Name: customer_account

| Nama Field | Type | Width | Description |
|------------|------|-------|-------------|
| Idnasabah  | Int  | -     | FK          |
| Norek      | Int  | -     | FK          |

Table Name: transactions

| Nama Field  | Type     | Width | Description  |
|-------------|----------|-------|--------------|
| Notransaksi | Int      | -     | PK, Not Null |
| Norek       | Int      | -     | FK           |
| Idnasabah   | Int      | -     | FK           |
| Jenistran   | Char     | 10    | Not Null     |
| Tgl         | Datetime | -     | Not Null     |
| Jml         | Int      | -     | Not Null     |

Tips:

To execute multiple SQL commands at once, add the GO command at the end of the command.

create table rekening
(norek int not null primary key,
kdcabang char(5) references cabang(kdcabang) on delete cascade on update cascade,
pin char(6) not null,
saldo int)
go

Next, create a customer_account and transaction table with an example like the SQL command above. After finishing, save the SQL commands by going to File >> Save. Save them in their respective folders on drive D:.

Create an employee table with a structure like the one below. Pay attention to the PK, FK, Identity, default and Null constraints.

| Nama Field | Type     | Width | Description                    |
|------------|----------|-------|--------------------------------|
| Kdkar      | Int      | -     | PK, Identity, Not Null         |
| Nmkar      | Varchar  | 40    | Not Null                       |
| Kdbag      | Int      | -     | FK dari tabel bagian, Not Null |
| Gender     | Char     | 1     | Not Null, Default Null         |
| Tgllahir   | Datatime | -     | Not Null                       |
| Mulaikerja | Datetime | -     | Not Null                       |
| Alamat     | Varchar  | 60    |                                |
| Kdkota     | Int      | -     | FK dari tabel kota             |
| Gaji       | Numeric  | 7,2   | Not Null                       |

Changing Table Structure

Changing the table structure can use the alter table command. In a banking database as an exercise, do the following SQL command:

create table pegawai
(idpeg int identity (1,1),
nama varchar(20) not null,
jk char(1) not null,
status bit default  1,
agama varchar(1) constraint cekagama check (agama in('I','K','P','B','H')),
primary key (idpeg))

Add, used to add fields to a table. Example:

alter table pegawai add alamat varchar(50)

If you want to add a name field in the employee table with a Unique constraint, the command is:

alter table pegawai add unique(nama)

Alter column, used to change the field structure in a table. For example, you want to change the width of the name field (60) to name (100). Example:

alter table pegawai alter column nama varchar(100)

To see the results of the changes in the table, give the command:

select * from information_schema.columns

Drop, used to delete fields in a table. Example:

alter table pegawai drop column jk

Drop table, is a command to delete a table in a database. Example:

drop table pegawai

To delete a table that is in another database you must specify the database name followed by the .dbo extension and the name of the table. unless you have used the use command. Example:

drop table kampusku.dbo.biodata

The command above means deleting the biodata table in my campus database.

Understanding the Database Design Cycle

Hi dev, tonight let's chat about "Database Design Cycle". First we need to know about Program data independence. What is it like, take a good look at this paradigm illustration.

The old paradigm tends more towards a data structure approach, while the new paradigm tends towards a database approach.

The importance of database design is also related to anticipating data redundancy problems.

Avoiding Data Inconsistency

Data isolation for standardization. Accessible by multiple users (multiuser).

Data Security. Addressing data integrity issues.

Improving data sharing capabilities and application development productivity as well as achieving data standardization and quality. Data quality greatly influences the output in the form of quality information.

Database Application Life Cycle

Source

Database System (IKB112101), by Wahyu Widodo.

Database System Normalization

Normalization is a technique in the logical design of a database that groups the attributes of a relation to form a good relation structure (without redundancy).

Normalization is the process of forming a database structure so that most ambiguities can be eliminated.

1. Purpose of Normalization

  • Optimization of table structures
  • Increase access speed
  • Reducing redundancy
  • Avoiding anomalies
  • Integrity increases

2. Modification Process Error (anomaly)

Delete Anomaly

namely the process of deleting a logical entity which causes the loss of information that is not logically related.

Insert anomalies

namely the process of inserting a logical entity that requires another logical entity.

Update anomalies

namely the process of updating data on a logical entity that results in changes in more than one place in a relation.

3. Dependencies

Functional Dependencies

Functional dependency notation is

A ⇒ B (A functionally determines B)

Functional Dependency Examples

Example:

  • NIM ⇒ StudentName
  • {Course, Student ID} ⇒ Letter Grade

Full Dependency

Full dependency indicates if there are attributes A and B in a relation, where:

  • B has full functional dependency on A
  • B does not have a dependency on subset A.

Transitive Dependency

Transitive Dependency is a condition where A, B, C are attributes of a relation where A ⇒ B and B ⇒ C. Then C is said to be transitively dependent on A through B.

4. Normalization Process

  • Describe in table form
  • Proceed to several levels according to certain requirements

Unnormalized Form

This form is a collection of recorded data, there is no requirement to follow a certain format, it can be incomplete or duplicated. Data is collected as it is according to its arrival.

Example of UNF form

1NF - First Normal Form

  • Absence of multi-valued attributes, composite attributes or combinations thereof.
  • Defines key attributes.
  • Each attribute in the table must be atomic (cannot be divided further).

Example of 1NF Form

2NF -- Second Normal Form

  • Already meets the first normal form (1NF)
  • All non-key attributes may only be dependent (functional dependency) on key attributes.
  • If there is a partial dependency then the attribute must be separated into another table.
  • There needs to be a connecting table or the presence of a foreign key for the attributes that have been separated earlier.

Example of 2NF Form

Third Normal Form

  • Already in second normal form (2NF)
  • There are no transitive dependencies (where a non-key attribute depends on another non-key attribute).

Example of 3NF Form

5. BCNF - Boyce-Codd Normal Form

BCNF form can be fulfilled if each functional dependency A ⇒ B has the condition that A is a superkey of the table concerned. The table must be decomposed based on the existing functional dependency, so that B becomes the super key of the decomposed tables.

Every table in BCNF is 3NF. However, not every 3NF is necessarily BCNF. The difference is, for functional dependency A ⇒ B, BCNF does not allow B as part of the primary key.

6. 4NF - Fourth Normal Form

4NF normal form is fulfilled in a table if the table has fulfilled BCNF form, and the table must not have more than one multivalued attribute. For each multivalued dependency (MVD) must also be a functional dependency.

Example of 4NF form

7. 5NF - Fifth Normal Form

The 5NF normal form is satisfied if it cannot have a lossless decomposition into smaller tables.

If the previous 4 normal forms are formed based on functional dependency, 5NF is formed based on the concept of join dependence, namely if a table has been decomposed into smaller tables, they must be able to be combined again (joined) to form the original table.

Source

Database System (IKB112101), by Wahyu Widodo.

Understanding Data Management

Data is a representation or recording of events, facts, or phenomena that occur in the real world.

Understanding Databases

A database is a collection of information about a particular subject, which is logically related, complete, and structured. A database provides a structure for information, and allows it to be used together by various different applications.

Database Classification

In its development, there are various database models, namely

  1. Hierarchical model
  2. network model
  3. relational model
  4. object oriented model

Of these models, the most developed and widely used today is the relational model. Object-oriented models are usually used for specific needs.

Introduction to Relational Databases

In the relational model, the database consists of relations or tables, which can be connected to each other. Here are the parts of a table model:

Fields/columns store data of the same type. The elements of a field are

  1. Name. In one table, there are no fields with the same name.
  2. Data type, can be a standard data type or another type provided by the database.
  3. Size. The size of a field is usually determined by its type, except for string types (strings of characters). In string types, the user determines the size used.
  4. A record is a representation of an entity recorded in a table. In a table (there should be) no two or more records that are exactly the same. Each record is unique in the table that contains it.

ER (Entity-Relationship) Model

In designing a relational database, an ER diagram is usually used. The components in the ER model are:

1. Entity Set, is a collection of entities that have the same attributes. Entity can be interpreted as something that can be distinguished from others. In the ER model, it is usually described as follows

2. Relationship Set, is a set of relationships between entities from two entity sets. Relationship Set is symbolized as follows

RDBMS

RDBMS (Relational Database Management System) is a software package that provides various services for designing, using, and maintaining relational databases.

Some of the common capabilities of modern RDBMS products include:

  1. Interface with user
  2. Database integrity maintenance
  3. Database security maintenance
  4. Backup and recovery
  5. Concurrent access control, etc.

Examples of modern DBMS products: IBM DB2, Oracle, MS SQL Server, MySQL, etc.


Post a Comment

Previous Next

نموذج الاتصال