3.- Ejercicio: Taller Mecánico

 "TALLER MECANICO"

Esquema Entidad-Relación (E-R)

 

Implementación del diseño lógico


CREATE DATABASE TALLER;
USE TALLER

CREATE TABLE IF NOT EXISTs `CLIENTE`(
`RFC` varchar(10) not null PRIMARY KEY,
`Nombre` varchar (60) not null,
`Direccion` varchar (60) not null,
`tel` varchar(10)
)ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `MECANICO`(
`Idmec` varchar(10) not null PRIMARY KEY,
`Nombre` varchar (60) not null,
`Direccion` varchar (60) not null,
`tel` varchar(10),
`Costoxhora` DECIMAL(2) not null
)ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `VEHICULO`(
`Matricula` varchar (10) not null PRIMARY KEY,
`Modelo` varchar (60) not null,
`Color` varchar (60),
`Fecha_ent` datetime not null,
`Hora_ent` datetime not null,
`RFC` varchar (10) not null,
`Mec` varchar(10) not null,
FOREIGN KEY (`RFC`) REFERENCES `CLIENTE` (`RFC`),
FOREIGN KEY (`Mec`) REFERENCES `MECANICO` (`Idmec`)
)ENGINE = InnoDB;


CREATE TABLE IF NOT EXISTS `REPUESTO`(
`Idrep` varchar(10) not null PRIMARY KEY,
`Descripción` varchar (60) not null,
`CostoUnit` int not null,
`PrecioUnit` int not null
)ENGINE = InnoDB;


CREATE TABLE IF NOT EXISTS `HOJAP`(
`Idhoja` varchar (10) not null PRIMARY KEY,
`concepto` varchar (100) not null,
`Cantidad` int not null,
`Reparacion` varchar(10) not null,
`Idmec` varchar(10) not null,
FOREIGN KEY (`Idmec`) REFERENCES `MECANICO` (`Idmec`),
FOREIGN KEY (`Reparacion`) REFERENCES `REPUESTO` (`Idrep`)
)ENGINE = InnoDB;


CREATE TABLE IF NOT EXISTS `FACTURA`(
`Nofact` varchar(10) not null PRIMARY KEY,
`Fecha` datetime not null,
`Imp_pesos` decimal(2) not null,
`Imp_dol` decimal(2) not null,
`RFC` varchar(10) not null,
`Idhoja` varchar(10) not null,
FOREIGN KEY (`RFC`) REFERENCES CLIENTE (`RFC`),
FOREIGN KEY (`Idhoja`) REFERENCES HOJAP (`Idhoja`)
)ENGINE = InnoDB;

INSERT INTO `CLIENTE` VALUES ('RFLM019890','Moon Light','ECATEPEC','57492608');

INSERT INTO `MECANICO` VALUES
     ('TTT00012','CARLOS LOPEZ','ATIZAPAN','554545454','50.50'),
     ('AHHASDHSDS','ANTONIO SANCHEZ','ECATEPEC','26462010','62.50');

SELECT * FROM CLIENTE;


INSERT INTO `CLIENTE` VALUES
     ('ABCD445512','SRITA_CHERRY','ARAGON','51525857'),
     ('RDC1101986','RULO DOM','CHICOLOAPAN','58520720');