Here we will be dealing about the various commands to work with in the DBMS. The DBMS which we are using here is MYSQL.
Creating and Deleting a Database :
The DBMS can contain any number of databases. Here we can consider a database for cars. So how will you create one...?
CREATE DATABASE [ IF NOT EXISTS ] cars ;
This command helps in creating a database called cars. the IF NOT EXISTS is used to check the database whether another database by the same name exists. This is optional but helps in preventing errors in-case another database by the same name exists. To use this database we should use the command,
USE cars ;
This helps in fetching the database cars and then we can create tables in the database and work on it.
To delete a database from the DBMS we can use the command,
DROP DATABASE [ IF EXISTS ] cars ;The IF EXISTS is again an optional command where you can check in prior whether the database already exists or not. DROP is a risky command to use as it deletes the database along with the entire tables inside the database.
Creating the tables :
Once you enter into the database you can create tables inside it. You need to be clear about various things before you create a table. We will discuss various things in detail before we know about the syntax of create tables.
a ) Data Types .
Data Types offer a lot of data types to work on. Each column will contain a particular data type. The data types can be NUMERIC, STRING, DATE AND TIME, SPATIAL. Picking a data type for a column determines on 1) How do you want to represent the data
2) Whether it is a fixed length or variable length.
3) If there is a need to index the data
4) How MySQL will compare the values in the data types.
b) Storage Engines .
MySQL offers various types of storage engines. The type of storage engines which are offered innoDB, MyISAM, BDB, HEAP, MERGE. The default engine which will be used is MyISAM. It provides full support to indexing but it is not transaction safe. Only innoDB and BDB are transaction safe.
MyISAM is used when you need fast access of data without any need to concentrate on foreign key constraints. InnoDB supports foreign key constraints.
We can change the table type(engine) on your need.
InnoDB vs MyISAM :
- InnoDB provides referential integrity(foreign key constraints)
- InnoDB provides row level locking. Hence it is used in write intensive applications.
- MyISAM is simple and hence it is generally preferred for read intensive applications.
- Genrally MyISAM is faster as it uses full-text indexing.
Primary key is the key which makes it possible to uniquely identify a record in a table. The primary key can be an attribute(column) or a set of attributes(columns).
Example: A car database can contain a car table. The car table needs a attribute to differentiate between two records. This can be achieved using having a car_id. This id should be unique and NOT_NULL.
Usually a table should contain primary key. If you don't have any primary key it wont be an error. Languages like Microsoft Access will maintain a default Primary key with an auto number data type.
Foreign keys are primary keys of another table. Foreign key comes to use to maintain relationships between tables. Foreign keys are used to cross reference between two tables.
Example: Coming back to the car database. There can be a manufacturer table which contains a manufacturer_id as primary key in it. Each car will have a manufacturer associated with it. This relation can be maintained by having a new column in car table known as manufacturer_id. Now, it will be easy to know the manufacturer of the car and also no car can contain unregistered manufacturer.
d) Identifying Nullable and NOT NULL fields in database :
There are certain situations when you don't need to specify certain fields and there are certain attributes which should not be left unfilled. For example a car may have a owner or may not have a owner. So it is not always needed to fill this attribute of the record. But each car should have tires so the tire_company_id should be filled and should not be left unfilled.
We can also have a Default field which allows a record to take a default value in-case it wasn't filled during insertion.
e) Identify exact data type for fields :
This is an important aspect in creating the table. Now, lets come back to car table. Here car_id can be a largeint as there is a need to store large number of cars and each record should have a unique identifier. The car_model indicating the model of the car should be a string data type. In that it can be a a varchar(10) - 10 letter characters. Because no model of a car can be more than 10 letters. So having some common sense and practical knowledge will let you pick the data types and fields.
f) Naming Conventions for Column Names:
The basic things to follow for naming columns are
1) To check whether the naming makes sense to you,
2) To check if the naming makes sense to others,
The first point is because if you see your code code few months later you may get confused. The second is because when many people are working with you the naming should be clear to both and the other people who uses it.
The preferred way of naming are using singular words and preventing mentioning the table names as a prefix of the attribute.
e) How to determine which field to index :
Creating index will slow down the entire database as there is a need to maintain index and there is overheads involved in creating and deleting the index. Generally primary fields are indexed but non primary fields are sometimes indexed too.
Index helps in improving the search if the value searched is same as the value required.
Example: If the car_id is indexed queries requiring the car_id's can be executed fast. But if there is a query regarding car_id and the model_name then the index wont be useful.
Create index by keeping in mind the query performance and the maintenance of the records.
No comments:
Post a Comment