Hi, we learned the conceptual and logical design during past two weeks. The physical design is the final step, which is SQL programming in order to create, populate, and query a relational database. During this session, we will learn how to study database management and how to generate DDL. However, we need to learn some physical database concepts first. The structured query language, SQL is a declarative standard and set oriented programming language used to define, manipulate, and control objects of a relational database. SQL is composed by four programming languages. DDL, which means Data Definition Language which allow us to create the structures. DQL stands for Data Query Language allows to access the data through selection criteria, grouping, ordering data, et cetera. DML, Data Manipulation Language allows to have access to structures to delete, modify, and insert data. Some others include the DQL within DML. DCL, Language of Control of Access to data allows to give access permissions to authorized users. The database manager we will utilize within these weeks is MySQL. On this session, we will check the main sentences from the DDL in order to create database subjects. The sentence CREATE. I use to create database and its objects like table, index, views, stored procedures, functions, and triggers. When we create a database we need to specify its name. To use this statement you need a create privilege for the database. Create Schema is a synonym of create database. An error occurs if the database exists and you did not specify if not exists. An example of creating database called mydatabase could be, CREATE DATABASE mydatabase. There are more database commands for instance, after being created a database, you may want to verify the creation sentence with SHOW CREATE DATABASE or to show all the databases on the server with a command SHOW. Also, the query database we'd use to start creating tables on that database. When the database is not useful anymore and you need more space, you can use the command DROP DATABASE to delete a database. Be careful when executing a drop because it's not recoverable. We may check if a database exists before to drop it in order to avoid a message error with IF EXISTS condition. The CREATE command can be used to create tables. These tables can be temporary tables which are visible only within the current session, and are dropped automatically when session is closed or a persistent table which is the default. Next, we can see an example of creating a table Students. Creating Student table, observe that the type domain of each field is specified and enforced by the DMS whenever records are added or modified. Drop table removes the table definition and all the data of the table. There is a default value close in a datatype specification which indicates a default value for a column with one exception, the default value must be a constant. It cannot be a function or an expression. Remember the importance of primary and foreign key in order to establish the entity identity and the one to many and many to many relationships for referential integrity. Well, now it's time to specify which columns will be these keys with a primary and foreign key classes. Consider a compact disc music store. The CDs contain music and recorder disc is a table derived from the relationship between compact disc and music. The track distinguishes the music on the recorder disc. With MySQL, we can establish cascade, restrict, or nullification methods of referential integrity. Check constraints are useful when more general integrity constraints than keys are involved. As we can see, we can use queries to express constraints and they can be named. Indexes are graded on the basis of certain columns that are the key to directly access data. Indexes avoid sequential and slow data access. The following MySQL statement we'll create an index on out_id column for newauthor table. There are different types of indexes: unique, secondary, full-text, and spatially index. We will talk about them next week. It may be possible that once you have created a table, you realize that some changes you need are required. The alter sentence, altered the structure of the existing database. You are able to add columns, indexes constraints, et cetera. We can see from this example that the schema of Students is altered by adding a new field. Every tuple in the current instance is extended with that null value in the new field. Now that we have learned the main data definition language commands, you may want to create your first database right away.
