­­Bazy dany 2 – projekt.

Apteka.

Autor: Piotr Musiel 184812

Prowadzący: Dr inż. Robert Burduk

1. Temat oraz cel zastosowania bazy danych.

• Baza danych dla apteki stacjonarnej ma na celu poprawę jakości oferowanych usług oraz usprawnienie zarządzania asortymentem.

2. Zakres i kontekst projektu.

• W projektowanej bazie będą przetrzymywane dane dotyczące parametrów i ilość dostępnych leków. Dodatkowo baza danych będzie obejmować recepty wraz ze stopniem refundacji. Baza danych będzie obejmować również klientów, pracowników i lekarzy. Na podstawie takich informacji można podejmować odpowiednie kroki zarządcze oraz kontrolne.

3. Wymagania funkcjonalne.

• Użytkownikiem jest osoba obsługująca beze danych, czyli farmaceuta lub technik farmacji. Ma dostęp do wszystkich parametrów leków oraz ilości i ceny jak i informacje od tym czy lek jest na receptę. Na podstawie recepty pracownik wylicz zniżkę dla klienta.

• Pracownik posiada prawo do wprowadzania zmian w bazie.

• Osoba obsługująca bazę danych może przeglądać oraz modyfikować dane dotyczące zamówień (nazwa, ilość, cena,).

4. Model logiczny bazy danych.

   1. Metodyka oraz notacja.

Poniżej zostało przedstawione ogólny sposób oznaczania związków w systemach bazodanowych.

• Opcjonalność

• K
  rotność

  • 1: 1 („jeden do jeden") – encji odpowiada dokładnie jedna encja,

  • 1: N ("jeden do wielu") – encji odpowiada jedna lub więcej encji,

• M: N ("wiele do wielu") – jednej lub więcej encjom odpowiada jedna lub więcej encji.

Poniższe symbole związków występują w opracowanym diagramie ERD.

2. Model związków encji

Diagram ERD bazy danych dla Apteki.

3. Określenie reguł poprawności:

• Pracownik, lekarz oraz klient mogą posiadać tylko jeden adres.

• Recepta może posiadać tylko jeden lek.

• Recepta musi mieć wypełnione wszystkie pola.

• W tabeli Lek w kolumnie uwagi należy uwzględnić wszystkie dodatkowe informacje np.: sposób przechowywania, sposób transportowania, kruchość, podatność na światło.

• Klient może składać zamówienia z receptą oraz bez recepty.

• Zamówienie nie może zawierać jednocześnie lek_id oraz recpeta_id.

5. Model implementacyjny

   1. Wyzwalacz

W celu udoskonalenia działania bazy danych został stworzony wyzwalacz. Wyzwalacz został zaimplementowany dal tabeli Zamówienie i ma on na celu nie dopuścić do tego, aby jednocześnie lek_id i recepta_id były większe od zera oraz aby nie były one jednocześnie NULL..

alter TRIGGER dbo.[test]

ON dbo.Zamowienie

for INSERT

AS

declare @lek int;

declare @rec int;

declare @licz int;

select @lek = i.lek_id from inserted i;

select @rec = i.recepta_id from inserted i;

select @licz = i.zam_ID from inserted i;

IF @lek > 0 AND @rec is null

begin

PRINT 'Zamówienie wprowadzono poprawnie'

end

if @lek is null AND @rec > 0 begin

PRINT 'Zamówienie wprowadzono poprawnie'

end

if @lek >0 AND @rec > 0 begin

PRINT 'Nieprawidłowe zamówienie'

DELETE FROM dbo.Zamowienie WHERE zam_ID = @licz

end

if @lek is null AND @rec is null begin

PRINT 'Nieprawidłowe zamówienie'

DELETE FROM dbo.Zamowienie WHERE zam_ID = @licz

end

2. Test wyzwalacza

W ramach testu wprowadzono następujące komendy.

USE [Apteka]

GO

INSERT INTO [dbo].[Zamowienie] ([lek_id],[recepta_id] ,[pracownik_id])

VALUES (1,null,3)

INSERT INTO [dbo].[Zamowienie] ([lek_id],[recepta_id] ,[pracownik_id])

VALUES (null,2,2)

INSERT INTO [dbo].[Zamowienie] ([lek_id],[recepta_id] ,[pracownik_id])

VALUES (1,1,1)

INSERT INTO [dbo].[Zamowienie] ([lek_id],[recepta_id] ,[pracownik_id])

VALUES (null,null,3)

GO

Otrzymane informacje po uruchomieniu komendy.

Podgląd tabeli po wprowadzeniu komedy:

SELECT *

FROM [Apteka].[dbo].[Zamowienie]

3. Implementacja.

Poniżej została zaprezentowana baza danych przy użyciu kodu SQL.

USE [master]

GO

/****** Object: Database [Apteka] Script Date: 2013-06-10 01:32:10 ******/

CREATE DATABASE [Apteka]

CONTAINMENT = NONE

ON PRIMARY

( NAME = N'Apteka', FILENAME = N'E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Apteka.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

LOG ON

( NAME = N'Apteka_log', FILENAME = N'E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Apteka_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

GO

ALTER DATABASE [Apteka] SET COMPATIBILITY_LEVEL = 110

GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))

begin

EXEC [Apteka].[dbo].[sp_fulltext_database] @action = 'enable'

end

GO

ALTER DATABASE [Apteka] SET ANSI_NULL_DEFAULT OFF

GO

ALTER DATABASE [Apteka] SET ANSI_NULLS OFF

GO

ALTER DATABASE [Apteka] SET ANSI_PADDING OFF

GO

ALTER DATABASE [Apteka] SET ANSI_WARNINGS OFF

GO

ALTER DATABASE [Apteka] SET ARITHABORT OFF

GO

ALTER DATABASE [Apteka] SET AUTO_CLOSE OFF

GO

ALTER DATABASE [Apteka] SET AUTO_CREATE_STATISTICS ON

GO

ALTER DATABASE [Apteka] SET AUTO_SHRINK OFF

GO

ALTER DATABASE [Apteka] SET AUTO_UPDATE_STATISTICS ON

GO

ALTER DATABASE [Apteka] SET CURSOR_CLOSE_ON_COMMIT OFF

GO

ALTER DATABASE [Apteka] SET CURSOR_DEFAULT GLOBAL

GO

ALTER DATABASE [Apteka] SET CONCAT_NULL_YIELDS_NULL OFF

GO

ALTER DATABASE [Apteka] SET NUMERIC_ROUNDABORT OFF

GO

ALTER DATABASE [Apteka] SET QUOTED_IDENTIFIER OFF

GO

ALTER DATABASE [Apteka] SET RECURSIVE_TRIGGERS OFF

GO

ALTER DATABASE [Apteka] SET DISABLE_BROKER

GO

ALTER DATABASE [Apteka] SET AUTO_UPDATE_STATISTICS_ASYNC OFF

GO

ALTER DATABASE [Apteka] SET DATE_CORRELATION_OPTIMIZATION OFF

GO

ALTER DATABASE [Apteka] SET TRUSTWORTHY OFF

GO

ALTER DATABASE [Apteka] SET ALLOW_SNAPSHOT_ISOLATION OFF

GO

ALTER DATABASE [Apteka] SET PARAMETERIZATION SIMPLE

GO

ALTER DATABASE [Apteka] SET READ_COMMITTED_SNAPSHOT OFF

GO

ALTER DATABASE [Apteka] SET HONOR_BROKER_PRIORITY OFF

GO

ALTER DATABASE [Apteka] SET RECOVERY FULL

GO

ALTER DATABASE [Apteka] SET MULTI_USER

GO

ALTER DATABASE [Apteka] SET PAGE_VERIFY CHECKSUM

GO

ALTER DATABASE [Apteka] SET DB_CHAINING OFF

GO

ALTER DATABASE [Apteka] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )

GO

ALTER DATABASE [Apteka] SET TARGET_RECOVERY_TIME = 0 SECONDS

GO

EXEC sys.sp_db_vardecimal_storage_format N'Apteka', N'ON'

GO

USE [Apteka]

GO

/****** Object: Table [dbo].[Adres] Script Date: 2013-06-10 01:32:10 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Adres](

[adres_ID] [int] NOT NULL,

[miasto] [varchar](50) NULL,

[ulica] [varchar](50) NULL,

[nr_lokalu] [int] NULL,

[kod_pocztowa] [int] NULL,

CONSTRAINT [PK_Adres] PRIMARY KEY CLUSTERED

(

[adres_ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

/****** Object: Table [dbo].[Klient] Script Date: 2013-06-10 01:32:10 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Klient](

[pesel] [int] NOT NULL,

[nazwisko_k] [varchar](50) NULL,

[imie_k] [varchar](50) NULL,

[adres_id] [int] NULL,

CONSTRAINT [PK_Klient] PRIMARY KEY CLUSTERED

(

[pesel] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

/****** Object: Table [dbo].[Lek] Script Date: 2013-06-10 01:32:10 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Lek](

[lek_ID] [int] NOT NULL,

[nazwa] [varchar](50) NULL,

[cena] [float] NULL,

[ilosc_w_opakowaniu] [int] NULL,

[dostepna_ilosc_sztuk] [int] NULL,

[data_waznosci] [date] NULL,

[uwagi] [varchar](50) NULL,

CONSTRAINT [PK_Lek] PRIMARY KEY CLUSTERED

(

[lek_ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

/****** Object: Table [dbo].[Lekarz] Script Date: 2013-06-10 01:32:10 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Lekarz](

[lekarz_ID] [int] NOT NULL,

[imie_l] [varchar](50) NOT NULL,

[nazwisko_l] [varchar](50) NOT NULL,

[telefon] [int] NULL,

[specjalizaca] [varchar](50) NOT NULL,

[adres_id] [int] NOT NULL,

CONSTRAINT [PK_Lekarz] PRIMARY KEY CLUSTERED

(

[lekarz_ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

/****** Object: Table [dbo].[Pracownik] Script Date: 2013-06-10 01:32:10 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Pracownik](

[pracownik_ID] [int] NOT NULL,

[imie_p] [varchar](50) NOT NULL,

[nazwisko_p] [varchar](50) NOT NULL,

[telefon_p] [int] NULL,

[adres_id] [int] NOT NULL,

CONSTRAINT [PK_Pracownik] PRIMARY KEY CLUSTERED

(

[pracownik_ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

/****** Object: Table [dbo].[Recepta] Script Date: 2013-06-10 01:32:10 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Recepta](

[recepta_ID] [int] NOT NULL,

[refundacja] [decimal](1, 1) NULL,

[data_wydania] [date] NULL,

[pesel] [int] NOT NULL,

[lekarz_id] [int] NOT NULL,

[lek_id] [int] NOT NULL,

CONSTRAINT [PK_Recepta] PRIMARY KEY CLUSTERED

(

[recepta_ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

/****** Object: Table [dbo].[Zamowienie] Script Date: 2013-06-10 01:32:10 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Zamowienie](

[zam_ID] [int] IDENTITY(1,1) NOT NULL,

[lek_id] [int] NULL,

[recepta_id] [int] NULL,

[pracownik_id] [int] NOT NULL,

CONSTRAINT [PK_Zamówienie] PRIMARY KEY CLUSTERED

(

[zam_ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Klient] WITH CHECK ADD CONSTRAINT [FK_Klient_Adres] FOREIGN KEY([adres_id])

REFERENCES [dbo].[Adres] ([adres_ID])

GO

ALTER TABLE [dbo].[Klient] CHECK CONSTRAINT [FK_Klient_Adres]

GO

ALTER TABLE [dbo].[Lekarz] WITH CHECK ADD CONSTRAINT [FK_Lekarz_Adres] FOREIGN KEY([adres_id])

REFERENCES [dbo].[Adres] ([adres_ID])

GO

ALTER TABLE [dbo].[Lekarz] CHECK CONSTRAINT [FK_Lekarz_Adres]

GO

ALTER TABLE [dbo].[Pracownik] WITH CHECK ADD CONSTRAINT [FK_Pracownik_Adres] FOREIGN KEY([adres_id])

REFERENCES [dbo].[Adres] ([adres_ID])

GO

ALTER TABLE [dbo].[Pracownik] CHECK CONSTRAINT [FK_Pracownik_Adres]

GO

ALTER TABLE [dbo].[Recepta] WITH CHECK ADD CONSTRAINT [FK_Recepta_Klient] FOREIGN KEY([pesel])

REFERENCES [dbo].[Klient] ([pesel])

GO

ALTER TABLE [dbo].[Recepta] CHECK CONSTRAINT [FK_Recepta_Klient]

GO

ALTER TABLE [dbo].[Recepta] WITH CHECK ADD CONSTRAINT [FK_Recepta_Lek] FOREIGN KEY([lek_id])

REFERENCES [dbo].[Lek] ([lek_ID])

GO

ALTER TABLE [dbo].[Recepta] CHECK CONSTRAINT [FK_Recepta_Lek]

GO

ALTER TABLE [dbo].[Recepta] WITH CHECK ADD CONSTRAINT [FK_Recepta_Lekarz] FOREIGN KEY([lekarz_id])

REFERENCES [dbo].[Lekarz] ([lekarz_ID])

GO

ALTER TABLE [dbo].[Recepta] CHECK CONSTRAINT [FK_Recepta_Lekarz]

GO

ALTER TABLE [dbo].[Zamowienie] WITH CHECK ADD CONSTRAINT [FK_Zamówienie_Lek] FOREIGN KEY([lek_id])

REFERENCES [dbo].[Lek] ([lek_ID])

GO

ALTER TABLE [dbo].[Zamowienie] CHECK CONSTRAINT [FK_Zamówienie_Lek]

GO

ALTER TABLE [dbo].[Zamowienie] WITH CHECK ADD CONSTRAINT [FK_Zamówienie_Pracownik] FOREIGN KEY([pracownik_id])

REFERENCES [dbo].[Pracownik] ([pracownik_ID])

GO

ALTER TABLE [dbo].[Zamowienie] CHECK CONSTRAINT [FK_Zamówienie_Pracownik]

GO

ALTER TABLE [dbo].[Zamowienie] WITH CHECK ADD CONSTRAINT [FK_Zamówienie_Recepta] FOREIGN KEY([recepta_id])

REFERENCES [dbo].[Recepta] ([recepta_ID])

GO

ALTER TABLE [dbo].[Zamowienie] CHECK CONSTRAINT [FK_Zamówienie_Recepta]

GO

USE [master]

GO

ALTER DATABASE [Apteka] SET READ_WRITE

GO

­­­