SQL is a database language allows you

• create database and table structures
• perform basic data management tasks (add, delete, & modify)
• perform complex queries designed to transform the raw data into useful information

SQL functions fit into two broad categories;

• data definition language (DDL) - commands to create database objects such as tables, indexes, views, and define access rights to those database objects
• data manipulation language (DML) - commands to insert, update, delete, and retrieve data within the database tables

Database model - to illustrate the commands

A schema is a logical group of database objects

• such as tables and indexes - that are related to each other. Usually, the schema belongs to a single user or application

A single database can hold multiple schemas that belong to different users or applications

Schemas usefulness;

• group tables by owner (or function)
• enforce a first level of security by allowing each user to see only the tables that belong to that user

Authentication is the process the DBMS uses to verify that only registered users access the database

INSERT INTO pts (name, geom) VALUES ('Empire State Building', ST_SetSRID(ST_MakePoint(36.985744, -1.748549),4269))

INSERT INTO lines (name, geom) VALUES ('Lincoln Tunnel', ST_SetSRID(ST_MakeLine(ST_MakePoint(-74.019921, -1.767119),ST_MakePoint(-74.002841, -1.759773)),4269));

INSERT INTO polys (name, geom) VALUES ('Central Park',ST_SetSRID(ST_MakePolygon(ST_GeomFromText('LINESTRING( 36.973057 -1.164356, 36.981898 -1.168094, 36.958209 -1.100621,36.949282 -1.196853, 36.973057 -1.164356)');

Queries about this Lesson, please send them to: jmwaura@jkuat.ac.ke

*References*

• Database Systems: Design, Implementation, and Project Management, Springer. Albert K W Yeung & G. Brent Hall
• Database Systems: Design, Implementation, and Management, 12th ed. Carlos Coronel & Steven Morris
• Database Modeling and Design; Logical Design, 5th ed. Taby Teorey et.al
• Fundamentals of database systems, 6th ed. Ramez Elmasri & Shamkant B. Navathe