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

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

Регуларни ентитети: : 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, birth, email, pass)
• Employee (id_person*(Person), id_library*(Library), position)
• Member (id_person*(Person), id_school*(SchoolType), id_membership*(MembershipType), id_library *(Library_DB), isPayedMembership, isPayedBook)
• Library_DB (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), downloaded)
• ReservationBook (id_resBook, id_book*(Book), is_person_member*(Member), take_date, return_date, isReservedBook)
• 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),coming_date, comingTime, leavingTime)
• Membership_of_SchoolType (id_membership*(MembershipType), id_school*(SchoolType))
• Who_Wrote_Book: (id_book*(Book), id_writer*(Writer))

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

kreiranje.sql​

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

polnenje.sql​

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

vintageLibrary_ER Diagram.png​