Scribd
Upload
4 views12 pages

SQL Script Development for Databases

This document describes the development of a basic database. It presents the objectives of creating scripts to manage the information in the database and analyze its operation. Then, it details SQL scripts to create tables, insert data, and perform simple queries on the database tables about books, copies, students, teachers, and loans.

Translated by

ScribdTranslations
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
4 views12 pages

SQL Script Development for Databases

This document describes the development of a basic database. It presents the objectives of creating scripts to manage the information in the database and analyze its operation. Then, it details SQL scripts to create tables, insert data, and perform simple queries on the database tables about books, copies, students, teachers, and loans.

Translated by

ScribdTranslations
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

BASIC DATABASE - DEVELOPMENT PHASE

JUAN DAVID DURAN BENJUMEA–1096228286


GROUP: 301330_22

TUTOR
IVAN ARTURO LOPEZ ORTIZ

NATIONAL OPEN AND DISTANCE UNIVERSITY–UNAD


SCHOOL OF BASIC SCIENCES, TECHNOLOGY, AND ENGINEERING
09/05/2018
BARRANCABERMEJA
INTRODUCTION

For the development of this activity, we must understand what a script is.
and what they are used for. We can define scripts as sets of
commands, that are given to a SQL engine to tell it what to do and
in what order should he do it.
OBJECTIVES

Develop scripts that allow for the management of information within


from the database.
Analyze the functioning of these scripts, as well as their importance.
what they have in the programming.
1. Script design for Data Definition Language (DDL)
Description Language DDL) with SQL Language (Structured Query
to create the database, tables, and the respective
fields according to the created data dictionary.

create table BOOKS


(BOOK_ID varchar (10) primary key,
BOOK_TITLE varchar (30) not null
BOOK_EDIT
integer
BOOK_EDITION integer
BOOK_AUTH varchar(40) not null
BOOK_SUBJECT varchar (20) not null
LIB_EXAMPLE integer
);

create table SAMPLES


(EJEMP_ID varchar (10) primary key,
varchar(10)
BOOK_ID varchar (10) REFERENCES BOOKS
);

create table STUDENTS

(ALUMN_ID varchar (10) primary key,


ALUMN_MATRI varchar (10) unique
STUDENT_NAME varchar(40) not null,
ALUMN_DIR varchar (20) not null,
STUDENT_PHONE char (10)

ALUMN_CEL varchar (10)


ALUMN_CPF varchar (10) not null,
ALUMN_RG varchar (10) not null,
char (30) unique
);

create table TEACHERS


(PROF_ID varchar (10) primary key,
PROF_NAME varchar (40) not null
PROF_DIR varchar (20) not null,
PROF_TEL char (10)
PROF_CEL varchar (10)
PROF_CPF varchar (10) not null,
PROF_RG varchar (10) not null,
PROF_EMAIL char (30) unique,
char (40)
);

create table USER


(USER_ID varchar (10) primary key,
NAME varchar (20) not null
APELL varchar (20) not null
);
create table LOAN
(PREST_ID varchar (5),
varchar (5)
USER_ID varchar(5)
date
DEV_DATE date,
PREST_EJMPNUM integer,
CONSTRAINT pk_LOAN
PRIMARY KEY (PREST_ID),
CONSTRAINT fk_USER
FOREIGN KEY (USER_ID)
REFERENCES USER (USER_ID),
CONSTRAINT fk_EXEMPLARS
FOREIGN KEY (EJEMP_ID)
EXEMPLARY REFERENCES (EXEMP_ID)
);

create table EXAMPLE_RENTAL


varchar(10)
varchar (10)
varchar (10)
CONSTRAINT pk_EJEMPLAR_PREST
PRIMARY KEY (EJEMPLAR_PRESTID)
CONSTRAINT fk_EXEMPLAR
FOREIGN KEY (EJEMP_ID)
EXEMPLAR REFERENCES (EXEMPLAR_ID)
);
create table RETURNS
(DEV_ID varchar (10),
DEV_FECHAPRO date,
DEV_DATEENT
PREST_ID varchar (10)
CONSTRAINT pk_DEVOLUCIONES
PRIMARY KEY (DEV_ID),
CONSTRAINT fk_LOAN
FOREIGN KEY (PREST_ID)
LOAN REFERENCES (PREST_ID)
);

create table PRESDEV_EJEMP


(PRESDEV_ID varchar (10),
PRESTID_EXAMPLE
DEV_ID varchar (10)
CONSTRAINT PRESDEV_EXAMPLE
PRIMARY KEY (PRESDEV_ID)
CONSTRAINT fk_DEVOLUCIONES
FOREIGN KEY (DEV_ID)
REFERENCES RETURNS (DEV_ID)
);
2. Design of the Script for Data Definition Language
Data Manipulation Language (DML) with SQL (Structured Query Language)
Query Language) to populate the database tables.

Example in which data is inserted into the teachers' table

INSERT INTO TEACHERS (TEACH_ID, TEACH_NAME, TEACH_ADDRESS,


PROF_TEL
PROF_TITLE
VALUES ('12324','ERMES GARCIA','STREET 74#32-
31','6020236','3115388303','3432444','5467744',
ERMESGTI82@[Link]
3. Design of the Script for generating simple queries about the
database tables.

It is based on the previous example, in which we already have data.


entered into the professors table. With select we can obtain
information about the data stored in the database. Such
Information can belong to one or more tables.

SELECT PROF_ID
FROM TEACHERS
CONCLUSION

For the development of this activity, it was necessary to understand the importance and the
structure that the codes had in the database. Sometimes it is required to repeat
various lines of code in search of syntax errors that may affect the
functioning of the program.
WEBGRAPHY

Data Definition and Modification Languages SQL


(uf1472) Madrid, SPAIN: IC Editorial. page. 5-55 Recovered
from:Cannot access external links.
070&query=

SQL Clearly Explained (Third Edition)


to SQL, Pages 65-74, ISBN 9780123756978. Publisher Morgan Kaufmann,
Recovered
ofUnable to access external links or content. Please provide the text you would like translated.
[Link]?_tid=71a1eed1-90e8-49f7-b8a1-
5458bc800c8f&acdnat=1541520324_51af5244e8b2978a14cc349c191d1fe2

You might also like