Логички и физички дизајн

Релациска шема (со мапирачка трансформација)

Регуларни ентитети: : Person, SchoolType, MemberShipType, PriceList, Employee, Member, Library, ReservationBook, OnlineDowlnoad,Room, reservationPlace, Book, Genre, Review, Writer

• Примарни клучеви: underline and bold
  
• Надворешни клучеви: bold, со ознака * и до нив стои во заграда на која табела покажуваат
  
• Not null атрибути: bold
  
• Останати атрибути: без ознака
  

---

• Person (id_person, name, surname, address, town, phone_num)
• Employee (id_person*(Person), id_library*(Library), position)
• Member (id_person*(Person), id_school*(SchoolType), id_membership*(MembershipType), id_library *(Library), birth, email, pass)
• Library (id_library, name, city)
• SchoolType (id_school, school_type_name)
• MembershipType (id_membership, mem_stype_name)
• PriceList (id_price, id_membership*(MemberShipType), price)
• Genre (id_genre, name)
• Writer (id_writer, name)
• Review (id_review, id_book*(Book), rate, rent_num)
• Room: (id_room, id_library*(Library), places, free_places)
• OnlineDownload (id_onlineDown, id_book*(Book), id_person_member*(Member))
• ReservationBook (id_resBook, id_book*(Book), is_person_member*(Member), take_date, return_date)
• Book: (id_book, id_writer*(Writer), id_library*(Library), name, book_num, if_free, book_price, book_price_late)
• ReservationPlace (id_resRoom, id_room*(Room), id_person_member*(Member), date, duration)
• Membership_of_SchoolType (id_membership*(MembershipType), id_school*(SchoolType))
• Who_Wrote_Book: (id_book*(Book), id_writer*(Writer))

DDL скрипта за креирање и бришење на табелите

kreiranje.2.sql]

DML скрипта за полнење на табела со податоци

polnenje.2.sql]

Релациски дијаграм изваден од DBeaver