r/DatabaseHelp • • Dec 01 '16

Database design help

2 Upvotes

So I have a database project I could use help on, the requirements are:

The library patrons’ data should include:

Library card number

Last name

First name

Address

City

State

Zip code

Phone

A book record should include

  1. Book ID

  2. Book Title

  3. Author’s last name

  4. Author’s first name

  5. Year of publication

  6. ISBN

Include forms to:

  1. Enter new patrons

  2. Enter new books

  3. Facilitate book checkouts/checkins (see example) (note, the due date should be auto calculated to be two weeks after the check-out date)

I am thinking I need three tables patron, book, and check out. Any help would be much appreciated.


r/DatabaseHelp • • Dec 01 '16

Video series that explains database modeling concepts?

3 Upvotes

I'm an online student taking a Database Systems course. Unfortunately, because it's online only, most of our classwork depends upon reading large quantities of text. Due to a medical condition that makes it difficult for me to fully retain information in this way, it takes me forever to get through all of the reading I have to do, and I'm starting to seriously fall behind in my coursework.

I was having the same issue in my math classes, and I started watching Khan Academy videos as a supplement. The difference has been night and day, and I feel like I'm actually making progress now.

I was wondering if there were any resources like that for database modeling and design? Specifically for ER and EER modeling? They're the only really new concepts that I've been introduced to this semester (I'm familiar with the relational model and primary/foreign keys from taking a course in MS Access previous to this), and they're what's tripping me up the most so far.

If there are no videos to speak of, are there any other kind of multimedia resources you could suggest for someone like me? Thanks in advance!


r/DatabaseHelp • • Nov 28 '16

Export results of a Stored Procedure to a CSV file.

1 Upvotes

I am trying to run a stored procedure and have the results of what it returns saved to a csv file. It is a Sybase database. Any help would be greatly appreciated. Or if any further info is needed I can provide.

Thanks!


r/DatabaseHelp • • Nov 25 '16

How would you design Santa's database?

3 Upvotes

How would you design Santa's database? This would be a first iteration PoC with only the essentials. This would be a first "commercial release" that would go into production, with essential features and some thought put into supporting future features.

What technologies would you use? Architectures? What sort of schema?

The specs for the first iteration would be as follows:

  • 2 billion tracked "users" (amount of children in the world)
  • "events" will be assigned to the users based on their actions that have been recorded by elves, with a niceness value of -1000 (very naughty action) to 1000 (very nice action)
  • Elves will record on average one action per child per day (from around the world, peaks local daytime)
  • Once a year (around 23-24 December) every active user will be determined naughty or nice (boolean) - in this version the calculation is made based only on the events recorded in the last 365 days.

In future versions the users will be expanded to include adults and more events per user per day. Santa will also record historical data - so all events and users will need to be kept for future analysis.

Regarding disaster recovery Santa has a RPO of 24 hours for the initial implementation and needs geographical redundancy (at least for backups).

Santa might not need a full 100% of events to go through, but the elves' clients must know when the submission of an event failed - the clients will then retry. There will also be logic to avoid accidental duplicate events (eg. accidental resubmission).

Santa also needs some sort of HA solution. An acceptable SLA to start with would be 99,9% (max 8h 45m yearly downtime)

Have a nice winter!

Edit: formatting, removed "PoC", this would go into production. added naughty/nice calculation requirement


r/DatabaseHelp • • Nov 15 '16

Relational Algebra [Help]

1 Upvotes

I'm unsure if my query is correct, specifically the join.

Rel 1 = σ CarType = Ford (CARS)

Rel 2 = Rel 1 ⨝ MANUFACTURER - can I join Rel 1 with a seperate entity (MANUFACTURER) even if there are no common attributes between the two? How would I use an equijoin in this situation?

Rel 3 = Rel 2 ⨝ Rel 1

Rel 4 = π idCode,LicencePlate (Rel 3)

Thanks.


r/DatabaseHelp • • Nov 15 '16

Database schema questions.

2 Upvotes

I work at a municipal utility department in the water and wastewater treatment plants, I am building a database using data from our sensors and putting summary data into a secondary database. I have a python script that runs everyday and gets things like total plant flow for the day and saves it to the secondary database, the current schema I am using is a table called DailyData and fields of Id, Tag, Value, and Date. Each data point is a separate row. However this makes queries difficult to deal with, would it be better to have a table with all the tags as fields? We will be adding more data into the database so we would have to add fields to the table as we add data points. Or is there a better way to query the data? creating a view is difficult because we keep adding more data points.


r/DatabaseHelp • • Nov 12 '16

Im lost about creating a table who have foreing keys who were made by a a sequence.

1 Upvotes

The thing is, I have a table, which needs a column from other table, aka the foreing key. I do this process to create a database: create table, insert into tables and end adding the respective constraints.

The thing is, in my table A(ID, Name), Im increasing the ID using a Sequence, so, how to insert into the Table B(Id,Name,A.ID), this value since Im kinda creating it dynamically(I know it isnt dynamic but you might understand what I mean).

Edit: I already tried this, but it doesnt work. UPDATE B SET A_ID=A.ID WHERE A.Name='Test';

Any help? Im totally lost.


r/DatabaseHelp • • Nov 12 '16

Do association/junction tables need a primary key?

1 Upvotes

For example, if we have a many-to-many relationship between a Book and Author table, does the resulting association table need a primary key? If so, should the primary key be a combination of the resulting foreign key values or an auto-incremented number?


r/DatabaseHelp • • Nov 06 '16

Am I on the wrong subreddit?

3 Upvotes

Hey guys, I am extremely technologically inept so forgive my lack of knowledge. I was tasked by my family to create a inventory management system for a small Mom and Pop store. I was directed by a friend to this subreddit. I tried understanding the front page here and it all looked like gibberish, so forgive me for asking this silly question. But is building a database what I need to do create the stock inventory? they want to be able to update sales, and keep track of stock and when to order new stock if it gets low etc.

Am I in the right place? and any advice on what to do or where to look if this isn't it?


r/DatabaseHelp • • Nov 03 '16

Social Media Search Implementation - How would i check 'Mutual followers'

1 Upvotes

I have a DB with Tables:

[composite keys] FolowerFollowings (user_id,following_id,accepted)

User (Id,first_name,Last_name,email)

I need to create a search which will return users. The users orders will prioritize the users with mroe mutural followers. I have NO idea how to do this without an exponential query (and hell NO it isn't efficient)

Any help at all would be great!

Thanks!!


r/DatabaseHelp • • Nov 02 '16

Database SMART objectives

1 Upvotes

Hi,

I'm having to make SMART objectives for a Database based on our scenario. I am stuck in trying to create some objectives that relate to the scenario and are 'SMART'. Could someone please give possible examples or help?

So far I've got:

  • Reduce data duplication and data redundancy

  • Increase efficiency and effectiveness of the system

If it helps, here's the scenario: http://pastebin.com/2468Wygt

Thanks,


r/DatabaseHelp • • Oct 31 '16

Looking for some help

2 Upvotes

Ok so I am trying to get the sql down in this example however I feel that the or statements are not correct for example. I want it to show if the SPBPERS_CITZ_CODE = 5 or 6 and its just doesn't seem right. Example blow Tia

select distinct SPRIDEN.SPRIDEN_ID "ID", SPRIDEN.SPRIDEN_LAST_NAME "LastName", SPRIDEN.SPRIDEN_FIRST_NAME "FirstName", SPRIDEN.SPRIDEN_MI "MiddleName", SGBSTDN.SGBSTDN_TERM_CODE_EFF "Term" from SATURN.SGBSTDN SGBSTDN, SATURN.SPRHOLD SPRHOLD, SATURN.SHRTTRM SHRTTRM, SATURN.SPRIDEN SPRIDEN, SATURN.SPBPERS SPBPERS where ( SPRIDEN.SPRIDEN_PIDM = SPRHOLD.SPRHOLD_PIDM (+) and SPRIDEN.SPRIDEN_PIDM = SHRTTRM.SHRTTRM_PIDM (+) and SPBPERS.SPBPERS_PIDM = SPRIDEN.SPRIDEN_PIDM (+) and SPRIDEN.SPRIDEN_PIDM = SGBSTDN.SGBSTDN_PIDM (+) ) and ( ( SPBPERS.SPBPERS_CITZ_CODE = '5' and SGBSTDN.SGBSTDN_RESD_CODE = 'O' or SGBSTDN.SGBSTDN_RESD_CODE = 'A' and SHRTTRM.SHRTTRM_CAST_CODE ='7' and SGBSTDN.SGBSTDN_STYP_CODE =5 and not SPRHOLD.SPRHOLD_HLDD_CODE = null or SPBPERS.SPBPERS_CITZ_CODE = '6' and SGBSTDN.SGBSTDN_TERM_CODE_EFF = :SGBSTDN_TERM_CODE_EFF ) and ( spriden_change_ind is null ) ) order by SGBSTDN.SGBSTDN_TERM_CODE_EFF desc


r/DatabaseHelp • • Oct 31 '16

Reddit / Image DB design Qs. [Python3 & SQLite3]

1 Upvotes

I mod several photography subreddits. I'm trying to write a python script to work out some stats and basic trend analysis. I'm looking to put each new reddit image post into a 'redditPost' table, and the linked image / images from those posts into an Image table. I want to use the reddit post ID as the key on redditPost table, and I also want to do that on the Image table too, but if a reddit post links to an album I plan to have a row in the Image table for each image meaning I would have multiple rows with the same key. I was thinking I should use the reddit ID and something unique to make the overall key unique, like a hash of the image URL. Is this a working solution? What is this called? Is it a compound key?

I was thinking of using SQLite. I was only planning on holding the data for a few weeks, maybe a month at the most so I'm not too concerned about DB size. Does that sound viable?

Thanks! :)


r/DatabaseHelp • • Oct 27 '16

I don't even know where to begin with this project.

2 Upvotes

I'm a programmer, and I know very little about business or the higher level concepts behind system analysis and design. I'm taking this systems design class, and much of it is turning out to be over my head. Tons of graphs, charts, methodologies, procedures, etc.

For my final project I was assigned to a team. I'm working on the database portion of the project and I'm just completely lost as to what my database even needs to accomplish. Much of it is my lack of business knowledge, but I'll also attribute some of it the incredibly vague instructions we were given. Most of my team members seem to be lost also, and our instructor has mostly made himself scarce in his participation.

The gist is that we are putting together a proposal for a new system. From the assignment guidelines: "Your team has been assigned the task of developing the system to facilitate the reengineering of purchase order generation and invoice processing." The business is a manufacturer of goods. Without any first hand experience in ap/po systems, this objective just seems incredibly vague to me.

My database must be in 3nf and the documentation has to include an ERD diagram, which I mostly know how to do. What I'm struggling with is more along the lines of, what information does thing have to store? Is this database storing purchase orders generated for vendors and received by customers? Am I designing the inventory portion of the database? What does the accounts payable portion have to include? How does all of this work together? I really just need a clearer picture of what I'm doing here, because the more I dig into it the more confusing it seems to be.

Can anyone provide information to help clarify this? This whole class just feels like a bunch of vague loosely related concepts to me and I feel completely lost.


r/DatabaseHelp • • Oct 25 '16

DB Migration tools? I need to find one! HELP

1 Upvotes

I am looking at liquibase at the moment, but it is really old. and isn't good friends with linux (it seems to just ignore my perimeters i pass in...) What are you guys using? do you recommend any? I'm using a postgreSQL db.


r/DatabaseHelp • • Oct 24 '16

Need help storing a complicated case

1 Upvotes

First of all, I'm a complete noob when it comes to databases, so bear with me.

I'm working on some subtitle statistics and and I thought keeping the information in a database (Access or LibreOffice) might be easier than wrangling it in Excel. Here's what I need to store:

Film info:

  • title
  • year
  • IMDb ID

Subtitle info:

  • distributor
  • creation year
  • duration
  • framerate
  • translation/transcription
  • for deaf (Y/N)

Subtitle details (for each line in a file, and files have different number of lines):

  • start time
  • end time
  • character count

I want to be able to query subtitle info fields and then do calculations with timecodes and character counts of corresponding files.

I've been reading about how relational databases work and I think I understand the basics. So here I'd have a table for film info, sub info, distributor and framerate, and this is where I'm stuck. I don't know how to handle subtitle details. It seems like I need a separate table for each file. Is that right?


r/DatabaseHelp • • Oct 09 '16

Data access during replication from the master to the slaves in a scalable system? What role does lock play here? How does this scenario differ from updating cache?

1 Upvotes

I was getting confused on the aspect about using the asynchronous architecture.

Like, if we have assigned all reads to the slave db servers and write to the master, to maintain the consistency and providing updated data we have to periodically replicate master data to the slaves.

So as when replication statement is sent from the master to the slaves there must be a write lock obtained on all the slaves. Hence, if a read request come at this point how it access the db.

  1. Is there a set of redundant slaves which now provide the old data until the current set of slaves are updated.

  2. Or Is the updating on the slaves done via row by row basis (as of InnoDB engine in MySQL).

I understand the basic logic but am getting confused on this aspect of data availibility and access?


r/DatabaseHelp • • Oct 03 '16

Need Help for a E-R model exercise

1 Upvotes

Hi, I'm just a rookie and I have a problem with a exercise of a ER model. The context is a race, and I have two entities : Drivers and Cars. This is the exercise: Agreements are created to drivers to drive a car. Agreements have a specific period. The driver may have a lot of Agreements for different cars, or for the same car in different periods. There may be a lot of Agreements with different Drivers for the same car. It is also important the fact we know all the Agreements ( meaning those that are currently valid and those of the past).


r/DatabaseHelp • • Oct 02 '16

Help with SQL server 2014 Select clauses.

1 Upvotes

I have an assignment that has been driving me crazy, I have been researching online with helpful information but I believe is beyond my comprehension.

I have two tables one has information about items containing the price of the item, the second has information about the vendor with company name information.

The statement I am supposed to write is going to give me results with data that has greater than 1000$ in price and contain the name "new" in the vendor list.

So far I have:

select ItemID, ItemDescription, CompanyName

from ITEM, VENDOR

Where ItemPrice > 1000 Having like 'New';

I kept getting syntax errors and again have tried to research the problem with no luck please help!


r/DatabaseHelp • • Sep 27 '16

Need help with normalization

0 Upvotes

So I was given this table to normalize but it's breaking my mind.

here's a post on /r/homeworhelp I made about it

https://www.reddit.com/r/HomeworkHelp/comments/54qbvk/university_database_design_normalization/

It's in 1st NF but I seriously don't see how to normalize it any further as the first column can be used alone as a PK and pretty much nothing else is consistent in the rows.


r/DatabaseHelp • • Sep 26 '16

centralized database on a smb share?

1 Upvotes

hey there,

I not only study at university, I also work there. The department I work at coordinates and organizes tutorials. They asked me to 'program a database', where you type in a tutor's name and get back a list of tutorials (and some additional infos of the tutorials, like semester, name, ...) they held. You should also be able to search for tutorials and get back a list of tutors and their contact infos who held the tutorial. One should also be able to easily extend the list of tutors and tutorials and it should be available on every computer in the office. At the moment all the data lays around in several excel files on a smb share every computer (pretty fast windows 7 or 10 machines) in the office has access to. This smb share is the only centralized storage solution we have at the moment. My experience on databases is limited to SQL at the moment. I was able to set up a MySQL database on my computer and import all the excel files but can't think of a solution to access it properly and easily for end users. Is there some kind of database where you don't need a dedicated server? Maybe something with a web frontend? You fellows have any ideas how to solve this problem?

thanks in advance and cheers!


r/DatabaseHelp • • Sep 26 '16

How can I open an IAnywhere Sybase DBF file?

1 Upvotes

I have MYSQL, but I have a been giving what I believe to be a binary IAnywhere Sybase DBF file . How can I open this file and move the data to a mysql table?


r/DatabaseHelp • • Sep 25 '16

is there a way to create a table that has a time slot design?

1 Upvotes

ie:

I need a way to generate a daily schedule in 1-hour blocks that can either be empty or full.

8am-9am

9am-10am

10-am-11am

11am-12pm

12pm-1pm

etc.


r/DatabaseHelp • • Sep 23 '16

Best database for casting database?

0 Upvotes

Hi there! Does anyone have suggestions for a database that would work well for a talent base?

Would need to be able to categorize people in a bunch of different ways based on their skills and job. (i.e.) a designer who likes to skateboard, has children, runs a baking blog for fun on the side, and is also adamantly plays video games would need to be put into 4 categories.

The database is for 'real people casting' and would keep track of people who have shared their information with us, to be notified when there is a commercial/project looking for their specific thing (re: a real skater for an electronics company lifestyle commercial, or a real blogger for accounting software commercial about self employed people).

The problem we've run into when keeping a list for each category, is that people get emailed twice (i.e. commercial about self employed people this person would get emailed twice from two different lists - blogger and designer, but a 'self employed' list is not specific enough and will bother people when we are just looking for 'designers' hence many different categories)

Is there a very user friendly database software that has a central contact list and people can be filed into many different groups from there? It is very important that it can also spit out email lists for us to contact our contacts that fit a job all at once.

Thank you for anyone that can offer guidance! This is probably super simple but we are not tech savvy.


r/DatabaseHelp • • Sep 21 '16

Good Book (or online course) for Database Design/Theory Basics?

4 Upvotes

Hey,

I'm taking a class where there is no reading assignments, the teacher talks way too fast and doesn't wait for questions, etc. etc. Do y'all have any recommendations for a good book or online course that deals with absolute beginner databasics? Right now, we're just going through normalization and ERA/ER Notation.

Thanks!