Database Systems with SQL - Full Course - Database Systems - Database Design Process - Analysis, Logical and Physical Phase

A database design is the organization of data, such as tables, columns, data types, and indexes. Database design involves classifying data and identifying relationships between tables. For simple databases, the database design process is typically informal developed quickly without process . However, larger and more complex databases typically have 3 phases: 1. Analysis 2. Logical design 3. Physical design The analysis phase (also called the conceptual design, entity-relationship or ER modeling, or requirements definition phase) specifies the requirements of the database, irrelevant of which database system will be used. The database requirements are represented as entities, relationships, and attributes. An entity is usually a noun (person, place, thing) or an activity. A relationship is the link between the entities. An attribute is a property that describes an entity. The logical design phase implements the database requirements and is depicted in a table diagram and the specified SQL is called a database schema. In a relational database systems, logical design converts entities, relationships, and attributes into tables, keys, and columns. A key is a column used for identifying individual rows of a table. Tables, keys, and columns are specified in SQL with CREATE TABLE statements. Table diagrams are more detailed versions of ER diagrams: - Rectangles are tables, with table names at the top. - Columns are the text within rectangles and below table names. - Keys are solid bullet points (●). Columns that refer to keys are empty bullets (○) with arrows. The physical design phase optimizes the data by adding indexes and specifies how to organize the tables on storage. Ex: Rows of a table may be sorted on the values of a column and stored in sort order. Physical design is specified with SQL statements such as CREATE INDEX and, like logical design, is specific to a database system. - Diagrams for physical design are not typically used Differences - Logical design affects the query result. - Physical design can affect query processing speed but never affect the query result. This is called data independence, and is a major advantage of relational databases. An ER diagram helps to illustrate entities, relationships, and attributes - Entities are represented by rectangles, with the entity name at the top. - Relationships are represented by lines between rectangles. Attributes are described using text inside the rectangle. Table diagrams are more detailed versions of ER diagrams: - Rectangles are tables, with table names at the top. - Columns are the text within rectangles and below table names. - Keys are solid bullet points (●). Columns that refer to keys are empty bullets (○) with arrows. Subscribe to Appficial for more programming videos coming soon. Also, don't forget to click LIKE and comment on the video if it helped you out!