Dealing with databases
What is a database…?
The database is an organized collection of structured data to make it easily accessible, manageable and update. In simple words, you can say, a database in a place where the data is stored. The best analogy is the library. The library contains a huge collection of books of different genres, here the library is database and books are the data.
In layman terms, consider your school registry. All the details of the students are entered in a single file. You get the details regarding the students in this file. This is called a Database where you can access the information of any student.
Database in day to day life
Why we need databases
A database is a collection of data, usually stored in electronic form. A database is typically designed so that it is easy to store and access information.
A good database is crucial to any company or organization. This is because the database stores all the pertinent details about the company such as employee records, transactional records, salary details etc.
When we use it
A database is needed if you have multiple processes (users/servers) modifying the data. Then the database serves to prevent them from overwriting each other’s changes. You also need a database when your data is larger than memory.
What is the importance of database?
Databases are a collection of organized information that can easily be accessed, managed and updated. Database systems are very important to your business because they communicate information related to your sales transactions, product inventory, customer profiles and marketing activities.
Designing database
What is Database Design?
Database Design is a collection of processes that facilitate the designing, development, implementation and maintenance of enterprise data management systems. Properly designed database is easy to maintain, improves data consistency and are cost effective in terms of disk storage space. The database designer decides how the data elements correlate and what data must be stored.
The main objectives of database designing are to produce logical and physical designs models of the proposed database system.
The logical model concentrates on the data requirements and the data to be stored independent of physical considerations. It does not concern itself with how the data will be stored or where it will be stored physically.
The physical data design model involves translating the logical design of the database onto physical media using hardware resources and software systems such as database management systems (DBMS).
Important of database designing
It helps produce database systems
- That meet the requirements of the users
- Have high performance.
Database designing is crucial to high performance database system.
Note, the genius of a database is in its design. Data operations using SQL is relatively simple
Database development life cycle
The database development life cycle has a number of stages that are followed when developing database systems.
The steps in the development life cycle do not necessarily have to be followed religiously in a sequential manner.
On small database systems, the database system development life cycle is usually very simple and does not involve a lot of steps.
In order to fully appreciate the above diagram, let’s look at the individual components listed in each step.
Requirements analysis
- Planning — This stage concerns with planning of entire Database Development Life Cycle It takes into consideration the Information Systems strategy of the organization.
- System definition — This stage defines the scope and boundaries of the proposed database system.
Database designing
- Logical model — This stage is concerned with developing a database model based on requirements. The entire design is on paper without any physical implementations or specific DBMS considerations.
- Physical model — This stage implements the logical model of the database taking into account the DBMS and physical implementation factors.
Implementation
- Data conversion and loading — this stage is concerned with importing and converting data from the old system into the new database.
- Testing — this stage is concerned with the identification of errors in the newly implemented system. It checks the database against requirement specifications.
What is SQL and how does it work?
SQL is used to communicate with a database. According to ANSI (American National Standards Institute), it is the standard language for relational database management systems. SQL statements are used to perform tasks such as update data on a database, or retrieve data from a database.
How to Use SQL
While an application might be programmed in a language like Python, PHP or Ruby, databases are not configured to understand these. Historically, databases understand only SQL (though this has changed significantly in recent years). Because of this, learning SQL is almost a must if you intend to work in web development or app development.
Like other programming languages, SQL has its own markup. This makes it necessary for a programmer to learn SQL markup before they can use it effectively.
Besides markup, another feature unique to database programming is the concept of tables. A database may be represented as a number of tables. Each table has its own number of columns and rows and represents a set of data. Imagine a library. We could create a database that stores data about books in the library.
There are a few frequently used SQL commands you should be familiar with for database work. When working with databases, a programmer might write commands such as:
- CREATE DATABASE — to create a database
- CREATE TABLE — to create tables
- SELECT — to find/extract some data from a database
- UPDATE — make adjustments and edit data
- DELETE — to delete some data
These are just the most common commands. The more complicated the database is, the more commands you as the programmer will need to use.
These commands are used when writing queries–inquiries that allow you to operate data in databases. In other words, when you enter these commands in a database system, the system interprets the commands and processes them. The result could be, for example, a new record in the database, or the creation of a new database.
Here are some quick examples:
CREATE DATABASE name_of_a_database — creates a database;
CREATE TABLE name_of_a_table)
column1 data_type,
column2 data_type,
columnX data_type );
The first query is responsible for creating a new database. For example, an app like Facebook might contain databases for each of the following:
- Users — this database would store all the information about profiles of the users
- Interests — this database would hold all the different interests that can be used to track the hobbies that users are into
- Geographic locations — this database would hold all the cities around the world in which Facebook users live
Physical databases
A physical database is a refinement of the logical database design. In this phase, you examine how the user will access the database, and refine the database design by answering questions like: What data will I commonly use? Which columns in the table should I index based on data access?
A physical database model shows all table structures, including column name, column data type, column constraints, primary key, foreign key, and relationships between tables. Features of a physical data model include: Specification all tables and columns. Foreign keys are used to identify relationships between tables…
The purpose of building a physical design of your database is to optimize performance while ensuring data integrity by avoiding unnecessary data redundancies. During physical design, you transform the entities into tables, the instances into rows, and the attributes into columns.
Common characteristics of a physical data model:
· Describes data requirements for a single project or application.
· Specifies all tables and columns.
· Contains foreign keys used to identify relationships between tables.
· Physical considerations may cause the physical data model to be different from the logical data model.
