bases de datos 1 prof. daniel obando fuentes. creando una base de datos create database dbname;

Bases de Datos 1

Prof. Daniel Obando Fuentes

CREANDO UNA BASE DE DATOS

CREATE DATABASE dbname;

ENCODINGS

•Character encoding: Es un repertorio de caracteres que permiten representar un determinado lenguaje. •Tambien llamados character set, character map o codeset.•Cada caracter puede contener una cantidad diferente de bytes, permitiendo asi ampliar el rango de caracteres a representar.

ENCODINGS

•US-ASCII, code unit 7 bits.•UTF-8, code unit 8 bits.•EBCDIC, code unit 8 bits.•UTF-16, code unit 16 bits.•UTF-32, code unit 32 bits.

CHARSETS Y COLLATIONS

A character set is a set of symbols and encodings. A collation is a set of rules for comparing characters in a character set. Let's make the distinction clear with an example of an imaginary character set.

Suppose that we have an alphabet with four letters: “A”, “B”, “a”, “b”. We give each letter a number: “A” = 0, “B” = 1, “a” = 2, “b” = 3. The letter “A” is a symbol, the number 0 is the encoding for “A”, and the combination of all four letters and their encodings is a character set.

Suppose that we want to compare two string values, “A” and “B”. The simplest way to do this is to look at the encodings: 0 for “A” and 1 for “B”. Because 0 is less than 1, we say “A” is less than “B”. What we've just done is apply a collation to our character set. The collation is a set of rules (only one rule in this case): “compare the encodings.” We call this simplest of all possible collations a binary collation.

But what if we want to say that the lowercase and uppercase letters are equivalent? Then we would have at least two rules: (1) treat the lowercase letters “a” and “b” as equivalent to “A” and “B”; (2) then compare the encodings. We call this a case-insensitive collation. It is a little more complex than a binary collation.

CHARSETS Y COLLATIONS

In real life, most character sets have many characters: not just “A” and “B” but whole alphabets, sometimes multiple alphabets or eastern writing systems with thousands of characters, along with many special symbols and punctuation marks. Also in real life, most collations have many rules, not just for whether to distinguish lettercase, but also for whether to distinguish accents (an “accent” is a mark attached to a character as in German “Ö”), and for multiple-character mappings (such as the rule that “Ö” = “OE” in one of the two German collations).

CUAL ES EL MEJOR ENCODING?

Depende UTF8 soporta la mayoría de lenguajes

occidentales y versiones simplificadas de lenguajes orientales.

Unicode para Mandarin

ALTER DATABASE

ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_unicode_ci;

CREATE TABLE

CREATE TABLE table_name(column_name1 data_type(size),column_name2 data_type(size),column_name3 data_type(size),....);

ALTER TABLE

ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

PERSONS

CREATE TABLE Persons

(

PersonID int,

LastName varchar(255),

FirstName varchar(255),

Address varchar(255),

City varchar(255)

);

DROP

• DROP TABLE table_name• /* limpiar */• TRUNCATE TABLE table_name • DROP DATABASE database_name

CONSTRAINTS

CREATE TABLE table_name(column_name1 data_type(size) constraint_name,column_name2 data_type(size) constraint_name,column_name3 data_type(size) constraint_name,....);

CONSTRAINTS

NOT NULL – Indicates that a column cannot store NULL value UNIQUE - Ensures that each row for a column must have a unique

value PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Ensures

that a column (or combination of two or more columns) have an unique identity which helps to find a particular record in a table more easily and quickly

FOREIGN KEY - Ensure the referential integrity of the data in one table to match values in another table

CHECK - Ensures that the value in a column meets a specific condition DEFAULT - Specifies a default value when specified none for this

column

NOT NULL

CREATE TABLE Persons

(

P_Id int NOT NULL,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255)

)

UNIQUE

CREATE TABLE Persons

(

P_Id int NOT NULL UNIQUE,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255)

)

UNIQUE (COMPAT)

CREATE TABLE Persons

(

P_Id int NOT NULL,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255),

CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)

)

UNIQUE CON ALTER

ALTER TABLE Persons

ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)

PRIMARY KEY

CREATE TABLE Persons

(

P_Id int NOT NULL PRIMARY KEY,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255)

)

PRIMARY KEY (COMPAT)

CREATE TABLE Persons

(

P_Id int NOT NULL,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255),

CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)

)

PK CON ALTER

ALTER TABLE PersonsADD PRIMARY KEY (P_Id)

FOREIGN KEY

CREATE TABLE Orders(O_Id int NOT NULL PRIMARY KEY,OrderNo int NOT NULL,P_Id int FOREIGN KEY REFERENCES Persons(P_Id))

FOREIGN KEY (COMPAT)

CREATE TABLE Orders

(

O_Id int NOT NULL,

OrderNo int NOT NULL,

P_Id int,

PRIMARY KEY (O_Id),

CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)

REFERENCES Persons(P_Id)

)

FK CON ALTER

ALTER TABLE OrdersADD CONSTRAINT fk_PerOrdersFOREIGN KEY (P_Id)REFERENCES Persons(P_Id)

CHECK

CREATE TABLE Persons(P_Id int NOT NULL CHECK (P_Id>0),LastName varchar(255) NOT NULL,FirstName varchar(255),Address varchar(255),City varchar(255))

CHECK (COMPAT)

CREATE TABLE Persons

(

P_Id int NOT NULL,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255),

CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')

)

CHECK CON ALTER

ALTER TABLE PersonsADD CONSTRAINT chk_Person CHECK

(P_Id>0 AND City='Sandnes')

DEFAULT

CREATE TABLE Persons

(

P_Id int NOT NULL,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255) DEFAULT 'Sandnes')

CREATE TABLE Orders

(

O_Id int NOT NULL,

OrderNo int NOT NULL,

P_Id int,

OrderDate date DEFAULT GETDATE()

)

DEFAULT (COMPAT)

CREATE TABLE Persons

(

P_Id int NOT NULL,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255),

CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')

)

DEFAULT CON ALTER

ALTER TABLE PersonsALTER COLUMN City SET DEFAULT 'SANDNES'

DROPPING

ALTER TABLE Persons DROP CONSTRAINT uc_PersonID ALTER TABLE Persons DROP CONSTRAINT pk_PersonID ALTER TABLE Orders DROP CONSTRAINT fk_PerOrders ALTER TABLE Persons DROP CONSTRAINT chk_Person ALTER TABLE Persons ALTER COLUMN City DROP DEFAULT DROP DATABASE Olympics; DROP TABLE sport;

EJERCICIO

Cree un script que genere la siguiente base de datos para el diagrama ER de OlympicsData UTF8 charset y collation

Cree los primary keys y los foreign keys Cree las tablas intermedias Utilice donde corresponda:

Default

Not null

Check

Unique

REFERENCIAS

http://dev.mysql.com/doc/refman/5.0/en/charset-general.html http://www.w3schools.com/sql/ http://msdn.microsoft.com/en-us/library/ms176061.aspx

TAREA: INVESTIGACIÓN

Cómo conectarse a una base de datos SQL Server desde Java

Envíe un script de ejemplo

SQL SERVER 2014

• Por default UTF-8

• Para soportar UNICODE se usa UTF-16. Para usar UTF-16• Usar tipos nchar, nvarchar, ntext

• Usar collations que tengan _CS

• CREATE DATABASE MyOptionsTest COLLATE Latin1_General_100_CS_AS_SC;