تصميم قواعد البيانات

Database Design

• Database design involves several stages: analysis, entity-relationship diagrams (ER diagrams), schema, Data Definition Language (DDL), and Data Manipulation Language (DML).
• Analysis focuses on identifying entities (objects) and their attributes (characteristics).
• Relationships between entities are crucial; examples include 1:1, 1:M, and M:M relationships.

Attributes

• Key attributes are unique identifiers. Examples include student ID, serial number, and social security number.
• Composite attributes are composed of multiple parts. An address, for instance, might include street, city, and zip code
• Multi-valued attributes can hold multiple values for a single entity. Examples include phone numbers and email addresses.
• Derived attributes are calculated from other attributes. An example is the average grade of students.

Entity Types

• Strong entities exist independently. Examples include students and courses
• Weak entities depend on other entities. Examples include dependent in the employee relation, as they depend on the employee for existence
• Recursive relationships occur when an entity relates to itself. An example is the hierarchical structure of an employee supervisory relationship.
• Ternary relationships describe relationships between three entities. For example, the relationship between a supplier, a project, and an item.

Database Implementation

• Converting an ER diagram to a relational database schema involves mapping entities to tables.
• Attributes become columns and relationships become foreign keys.

SQL

• SQL (Structured Query Language) is used to manage and manipulate data in databases.
• DDL (Data Definition Language) commands are used to define tables, columns, and constraints.
• DML (Data Manipulation Language) commands are used to manipulate data within the database. These include insert, select, update, and delete statements.

Constraints

• Constraints are rules applied to data integrity and consistency. Common constraints include:
  • Key constraints to ensure uniqueness.
  • not null to prevent missing values.
  • unique to prevent duplicate values.
  • check to enforce specific rules.
• default value is used to set default values to columns.

Data Types

• Data types specify the kind of data a column can hold. Note variances between VARCHAR(length) and CHAR(length).

Additional Notes

• Database management systems (DBMS) use SQL to interact with databases.
• Common database systems include SQL Server
• Multiple relationships exist in database design.
• Proper constraints are essential to maintain data integrity.
• DDL commands are used to create, modify, and delete tables and columns.
• DML commands are used to insert, select, update, and delete data from tables.
• Foreign keys establish relationships between tables.