Database Design Spring 2023 (McCann)
project代写 | lab代做 | database – 这是利用database进行训练的代写, 对database的流程进行训练解析, 涵盖了database等程序代做方面, 这个项目是lab代写的代写题目
CSc 460 database Design
Spring 2023 (McCann)
Homework
(100 points)
Overview: Becoming proficient in formulating useful database queriestakes practice. Knowing how to use pure Relational Algebra is useful background for learning SQL. To use SQL, you may not need to call the Relational Algebra operators directly, but you do need to specify the critical parts of them. It helps to know how those parts fit in to the query.
Software:Richard Leyton, then a student at Oxford Brookes University, wrote a simple DBMS called LEAP as a project. The current version of LEAP is 1.2.6 and runs reasonably well under the LINUX operating system. The syntax of its relational algebra commands differs a bit from what we use in class, but converting between the notations is not hard. There does exist a versionof LEAP for Windows, but I have never used it and so cannot recommend it.
To install LEAP into your lab account, heres what you need todo:
- From your home directory, type this:/home/cs460/spring23/leap/scripts/users/leapinstall
- When asked to supply the LEAP source directory, respond with this:/home/cs460/spring23/leap
- When asked to supply the target directory, just press Enter. It will default to a subdirectory named leapin your account.
To run leap, heres what you need to do:
- Change directory to your local LEAP bin subdirectory:cd leap/bin
- Run leap:./leap
- To select our database, give leap this command:use ebook
To execute the sample query Ive provided (inleap/database/ebook/source/sample.srcin your account), run leap (see the three steps above) and give this LEAP command: @ sample Please note that for this command to work, your source files must be in that directory (leap/database/ebook/source).
Heres a brief list of useful LEAP commands and their syntax:
Operation General Format Example of Use
Use usedatabase use rental
Select select (relation) (condition) r1=select (borrow) (amount=1000)
project project (relation) (attr. list) r2=project (spj) (sno,qty)
Join join (rel1) (rel2) (condition) j=join (spj) (p) (spj.pno=p.pno)
Union (relation) union (relation) u=(employee) union (manager)
Intersection (relation) intersect (relation) int=(employee) intersect (manager)
Difference (relation) difference (relation) m=(employee) difference (manager)
Cartesian Product (relation) product (relation) prod=(s) product (spj)
Display a Relation displayrelation display prod
Copy a Relation duplicate (relation) copyofs = duplicate (s)
Execute a Source File @filename @ sample
Quit LEAP quit quit
(Unfortunately (for you!), LEAP does not have the division operator.) Other LEAP commands can be learned by reading the online help (type:helpfrom within LEAP to get started) or the documentation files inthe doc and help subdirectories. Be aware that every attribute is of type STRING or INTEGER, and all constants have to be specified in single quotes (yes, including integers!).
Assignment:The database already contains some relations for an ebook database in LEAP format. Here are their schemas. book (isbn,title,edition,category,price,copyright,pages,pcode) encoding (isbne,format,drm) writer (wid,surname,givenname,wcity,wstate,wzipcode,phone,email) publisher (pid,name,address,pcity,pstate,pzipcode,url) authorship (isbna,aid,percentage) review (rid,isbnr,stars)
Ive underlined the primary key fields. Foreign keys should be easy to identify, as they have names similar to the corresponding primary keys. Looking at the data helps, too!
Your task is to create relational algebra queries for LEAP that correctly answer the following questions:
- What are the names of the publishers?
- What are the full names (given and surnames) of the writerswho live in California (CA)?
- What is the Cartesian Product of the writers cities and the publishers cities?
- (You mustnotuse JOIN for this query) What are the titles of the ebooks encoded in azw3?
- (You must use JOIN for this query) What are the titles of theebooks encoded in azw3?
- What are the surnames of the writers who have at least one oftheir books available as a PDF?
- For each book available without DRM, show its title, authors email, and publisher URL.
- What are the full names of the writers who have written one or more ebooks as a coauthor?
- Display the titles of the books that have no encodings.
- What are the titles of the ebooks that have a copyright more recent than 2015 or that have at least 500 pages (or both)?
- What are the names of the publishers of the books written by book authors who are also reviewers?
- What are the IDs of the reviewers who have reviewed all of Chouskys books? (Yes, this is thequery.)
Hand In:Submit your LEAP queries (as atarfile) using turnin. The submission folder iscs460h2.
Want to Learn More About LEAP?Visithttp://leap.sourceforge.net/(online help files!)
Other Requirements and Hints:
You can easily capture LEAPs output to a file by running LEAP within the script command. First type
script, then run LEAP, then typeexit. Everything you saw on the screen is saved in a file named
typescript. Helpful hint: Dont run a text editor from withinscript!
In LEAP, you can create a.srcfile (namedquery1.src, for example) in theleap/database/ebook/source
directory in your account, and type into it the sequence of operations needed to answer question #1,
above. To execute a file of LEAP commands, type @ followed by the name of the .src file you want
to execute. (For example: @ query1) Do this while running LEAP, of course. This is a convenient
mechanism for storing your queries and for easily creating your final output for submission on the due
date.
I have attempted to write solutions to all of the assigned queries, and believe them to be possible to
answer using LEAP. Feel free to help each other out with workarounds, etc., to LEAPs quirks, but write
your own queries. (If you pattern your script file(s) after thesample.srcfile Ive provided, the odds of
success improve.)
LEAP may have problems dealing with temporary relations of high degree; if you have problems, remove
extraneous attributes before performing joins.
When LEAP crashes, it cant clean up its temporary files, and as a result it can fail to restart. A simple
solution: Save copies of your.srcfiles, and reinstall LEAP.
And finally: Please remember that a correct answer is a query that produces the correct resultin a
logically correct way! Write queries that will work even if the relations contents change.